mvdriel
asked on
MySQL Count question
Hi Guys,
I have a MySQL-database setup with three tables:
Employees
ID - Primary key
Name - Text
Stations
ID - Primary key
Name - Text
Tests
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:
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?
I have a MySQL-database setup with three tables:
Employees
ID - Primary key
Name - Text
Stations
ID - Primary key
Name - Text
Tests
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
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?
ASKER
Hi mwvisa1,
I looked through this article, but don't really see the connection with what I'm trying to do (yet).
Please elaborate :)
I looked through this article, but don't really see the connection with what I'm trying to do (yet).
Please elaborate :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
which can probably also be written as avg(if(trainee=X),t1.score
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
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*
"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*
ASKER
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):
Thanks for helping me out!!
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
FROM SOCs t1
INNER JOIN SavedSOC t2 ON t1.id = t2.socid
GROUP BY t2.socid
Thanks for helping me out!!
ASKER
Hm.. SavedSOC = 'Tests' and Soc = 'Station'
ASKER
Great help that didn't just provide an answer, but tried to let me understand what was going on. GREAT!
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:
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.
I have an alternative showing @rownum approach, but the GROUP_CONCAT() is much simpler in comparison.
I hope those come in handy and help demonstrate how each article may apply in the future.
Good luck!
Best regards and happy coding,
Kevin
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
;
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`
;
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`
;
I hope those come in handy and help demonstrate how each article may apply in the future.
Good luck!
Best regards and happy coding,
Kevin
https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html
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. ;)