• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

MySQL Count question

Hi Guys,

I have a MySQL-database setup with three tables:
ID - Primary key
Name - Text
ID - Primary key
Name - Text
ID - Primary key
StationID - linked to Stations.id
EmpID - linked to employees.id
TrainerID - linked to employees.id
Score - Decimal

I need a select statement that gives me the following output
Station 1 - AVG score all trainers - AVG score trainer 1 - AVG score trainer 2 - etc
Station 2 - AVG score all trainers - AVG score trainer 1 - AVG score trainer 2 - etc

I know i can get the avg score for all trainers per station by using:
SELECT t2.Name, avg(t1.score) FROM Tests t1 INNER JOIN Stations t2 ON (t1.StationID = t2.ID) GROUP BY t1.StationID

Open in new window

I need to extend the query so it makes a seperate column for each trainer and the average score for this trainer (let's assume there are two trainers for now and sort the columns by the trainers employees.id)

The easiest way imo is to use multiple querys, but the result need to be bound to a control, so I need one resulting table.

Any suggestions on how to do this?
  • 4
  • 4
1 Solution
Kevin CrossChief Technology OfficerCommented:
I am thinking you need to start by creating a view ranking your test rows by trainer per station ID.
If you plan to manually input the two trainer IDs, then this is not need; however, from your description it sounds like it is ... please give that a look and let me know if it makes sense before we proceed. It will give me some time to finish me dinner too. ;)
mvdrielAuthor Commented:
Hi mwvisa1,

I looked through this article, but don't really see the connection with what I'm trying to do (yet).
Please elaborate :)
Kevin CrossChief Technology OfficerCommented:
Okay. To pivot or cross tab an average by trainer, i.e., get those under multiple columns then I would use conditional aggregates - http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_3527-A-SQL-Tidbit-Conditional-Aggregates.html. In your case, though the conditions did not appear to just be avg(case trainerid when 1 then t1.score end) as trainer one for different stations may be a different employee each time so you really need avg(case trainerid when {id that is first ranked by station id} then t1.score end). In order to get the ranking piece handled, I was suggesting you start with a view on the tests table that already handled that so that it would become as simple as what I showed in the first conditional aggregate example.

Does the link make sense now?

Was my reading of what you are doing incorrect?
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

if you do not know the list of trainers, it cannot be done

you may manage to get a "horizonal" view of your results using group_concat()
this will let you store all the averages in a single field
you can for example build the field to contain a dictionnary such as "avg_station1=X avg_station2=Y ..."

the alterative is to list all the trainers by selecting the trainers table and programatically build a very ugly query
each column def will look like sum(if(trainee=X),t1.score,0)/sum(if(trainee=X),1,0)
which can probably also be written as avg(if(trainee=X),t1.score,null)

i think the solution depends on the needs and the size of the dataset. it is very likely that group-concat mixed with a little programming is the way to go

... or you can obviously use a stored procedure to create a temporary table, and then loop through the other table incrementing counters, or perform one query per column to fill it up

best of luck

ps : if you want the exact queries, prease provide a mysqldump of the first 100 lines of your tests table or the whole database if you do not want to rebuild the joins. and please also provide information regarding which type of solution suits you
Kevin CrossChief Technology OfficerCommented:
Again, I agree with you, except you global never type statement. *laughing*
"if you do not know the list of trainers, it cannot be done" < In fact, it can be done using ranking. The sum(if(...)) syntax is just another form of conditional aggregates from what I showed. group_concat() can be used in the ranking as demonstrated in my article. *smile* The combination of the two approaches can yield a solution...so never say never. Ha. *laughing*
mvdrielAuthor Commented:
Hi mwvisa1,

the last article (about conditional aggregates) pointed me in the right direction.
I'm sure the resulting query isn't exactly what you tried to tell me, but it gets the job done.
Since there are only max 15 trainers the resulting query may be long, but is easily generated in the software's code.

My code (for one trainer only):
SELECT t1.name, concat( format( ifnull( avg( t2.score ) , 0 ) , 1 ) , '%' ) , 
CONCAT( format( ifnull( avg( CASE t2.TrainerID WHEN 314  THEN t2.score END ) , 0 ) , 1 ) , '%' ) AS train1
INNER JOIN SavedSOC t2 ON t1.id = t2.socid
GROUP BY t2.socid

Open in new window

Thanks for helping me out!!
mvdrielAuthor Commented:
Hm.. SavedSOC = 'Tests' and Soc = 'Station'
mvdrielAuthor Commented:
Great help that didn't just provide an answer, but tried to let me understand what was going on. GREAT!
Kevin CrossChief Technology OfficerCommented:
I am glad that helped. If the number of trainers groups, you can use MySQL prepared statements to assist you. You essentially build a query that gets you the distinct trainerid values. Using this query and concat, you build your SQL statement into a string/variable. Using prepared statement, you can prepare and execute the SQL represented by the variable.

Alternatively, you can use my GROUP_CONCAT() and FIND_IN_SET() example shown below and in ranking article to rank the values of the TrainerIDs even with just the 15 and show your columns accordingly.

I was mocking up these examples for you. Although you found your solution, maybe these will help future readers if not yourself.

Test data:
-- create and populate some sample employees
-- drop table Employees;
create table Employees(
   ID int primary key, 
   `Name` varchar(100)
insert into Employees(ID, `Name`) 
values(1, 'John Smith'),
      (2, 'Jane Doe'),
      (3, 'John Doe'),
      (4, 'Jane Smith')

-- create and populate some sample stations
-- drop table Stations;
create table Stations(
   ID int primary key, 
   `Name` varchar(100)
insert into Stations(ID, `Name`) 
values(1, 'Station 1'),
      (2, 'Station 2'),
      (3, 'Station 3'),
      (4, 'Station 4')

-- create and populate some sample tests
-- drop table Tests;
create table Tests(
   ID int auto_increment primary key, 
   StationID int, 
   EmpID int, 
   TrainerID int, 
   Score decimal(12,2)
insert into Tests(StationID, EmpID, TrainerID, Score)
select stn, emp, trn, rand()*100
from (
   select 1 as stn union select 2 union select 3 union select 4
) s, (
   select 1 as emp union select 2 union select 3 union select 4
) e, (
   select 1 as trn union select 2 union select 3 union select 4
) t, (
   select 1 as x from mysql.help_topic limit 10
) x10
where t.trn not in (e.emp, s.stn)
order by (rand()*100) desc
limit 100

Open in new window

Using GROUP_CONCAT() and FIND_IN_SET(). Maybe what you are doing is more along the lines of GROUP_CONCAT() of inner query without the GROUP BY. Then you will get 1-15. Whereas my original thought was you wanted trainer 1 (e.g., 314) for station 1 compared to trainer 1 (e.g., 278) for station 2 in the same column.
select s.`Name`
     , avg(t.score) AS AvgTotalScore
     , avg(case find_in_set(t.trainerid, r.ranking) when 1 then t.score end) AS AvgTrainer1Score
     , avg(case find_in_set(t.trainerid, r.ranking) when 2 then t.score end) AS AvgTrainer2Score
from Stations s 
join Tests t on s.ID = t.StationID
join (

select StationID, group_concat(distinct TrainerID order by ID) Ranking
from Tests
group by StationID

) r on s.ID = r.StationID
group by s.`Name`

Open in new window

I have an alternative showing @rownum approach, but the GROUP_CONCAT() is much simpler in comparison.
select s.`Name`
     , avg(t.score) AS AvgTotalScore
     , avg(case t.ranking when 1 then t.score end) AS AvgTrainer1Score
     , avg(case t.ranking when 2 then t.score end) AS AvgTrainer2Score
from Stations s 
join (

select @rn := case 
                 when t.StationID <> @stn then 1
                 when t.TrainerID = @trn then @rn
                 else @rn + 1
              end as Ranking
     , t.ID
     , @stn := t.StationID as StationID
     , t.EmpID
     , @trn := t.TrainerID as TrainerID     
     , t.Score
from Tests t, (select @rn := 0, @trn := null, @stn := null) r
order by t.StationID, t.TrainerID

) t on s.ID = t.StationID
group by s.`Name`

Open in new window

I hope those come in handy and help demonstrate how each article may apply in the future.

Good luck!

Best regards and happy coding,

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now