MySQL Count question

Posted on 2011-10-26
Last Modified: 2012-05-12
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
EmpID - linked to
TrainerID - linked to
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

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?
Question by:mvdriel
    LVL 59

    Expert Comment

    by:Kevin Cross
    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. ;)

    Author Comment

    Hi mwvisa1,

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

    Accepted Solution

    Okay. To pivot or cross tab an average by trainer, i.e., get those under multiple columns then I would use conditional aggregates - 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?
    LVL 25

    Expert Comment

    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
    LVL 59

    Expert Comment

    by:Kevin Cross
    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 never say never. Ha. *laughing*

    Author Comment

    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, 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 = t2.socid
    GROUP BY t2.socid

    Open in new window

    Thanks for helping me out!!

    Author Comment

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

    Author Closing Comment

    Great help that didn't just provide an answer, but tried to let me understand what was going on. GREAT!
    LVL 59

    Expert Comment

    by:Kevin Cross
    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,


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now