MySQL Count question

I am thinking you need to start by creating a view ranking your test rows by trainer per station ID.
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. ;)

Hi mwvisa1,

I looked through this article, but don't really see the connection with what I'm trying to do (yet).
Please elaborate :)

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

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*

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
FROM SOCs t1
INNER JOIN SavedSOC t2 ON t1.id = t2.socid
GROUP BY t2.socid

Select allOpen in new window

Thanks for helping me out!!

Hm.. SavedSOC = 'Tests' and Soc = 'Station'

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:

-- 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
;

Select allOpen 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`
;

Select allOpen 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`
;

Select allOpen 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,

Kevin