1

I have three different tables and I need to count how many rows has a specific value (COUNT(track)) then group by tracks.id and sort by only one result per user (tracks.uid).

My three tables:

`users`
+--------+-------------------+
| `idu`  | `username`        | 
+--------+-------------------+
| 567    | 'TrainingPuppy'   | 
| 687    | 'BathroomMakeover'|
| 45     | 'PoshNachos'      | 
| 15     | 'SewingButtons'   | 
+--------+-------------------+

`views`
+--------+------+---------+
| `id`   | `by` | `track` |
+--------+------+---------+
| 1      | 5    | 55      | 
| 2      | 5    | 55      | 
| 3      | 67   | 55      | 
| 4      | 6    | 29      | 
| 5      | 125  | 2       | 
| 6      | 5    | 698     | 
| 7      | 5    | 698     | 
+--------+------+---------+

`tracks`
+--------+-------+-----------------------+---------------------+
| `id`   | `uid` | `title`               | `time`              |
+--------+-------+-----------------------+---------------------+
| 2      | 15    | 'Worf is in the air'  | 2016-02-11 22:57:35 |
| 29     | 567   | 'Stargold'            | 2016-08-11 22:57:28 |
| 55     | 567   | 'No love liers'       | 2016-10-11 22:57:51 | 
| 698    | 567   | 'Lofe'                | 2016-11-11 22:57:44 |
+--------+-------+-----------------------+---------------------+

So I tried:

SELECT `views`.`track`, `tracks`.*, `users`.*, COUNT(`track`) as `count` 
FROM `views`,`tracks`,`users` 
WHERE `views`.`track` = `tracks`.`id` 
    AND `tracks`.`uid` = `users`.`idu`  
GROUP BY `tracks`.`uid` 
ORDER BY `count` 
DESC LIMIT 0, 20

RESULTS

+--------+-------+---------+---------------------+----------+-----------------------+---------------------+
| `id`   | `uid` | `count` | `username`          | `track`  | `title`               | `time`              |
+--------+-------+---------+---------------------+----------+-----------------------+---------------------+
| 29     | 567   |  6      | 'TrainingPuppy'     | 29       | 'Stargold'            | 2016-10-11 22:57:51 | 
| 2      | 15    |  1      | 'SewingButtons'     | 2        | 'Worf is in the air'  | 2016-02-11 22:57:35 |
+--------+-------+---------+---------------------+----------+-----------------------+---------------------+

Instead I need to select only 1 track (the most counted in views table) of each user. Something like below:

+--------+-------+---------+---------------------+----------+---------------------+---------------------+
| `id`   | `uid` | `count` | `username`          | `track`  | `title`             | `time`              |
+--------+-------+---------+---------------------+----------+---------------------+---------------------+
| 55     | 567   |  3      | 'TrainingPuppy'     | 55       | 'No love liers'     | 2016-10-11 22:57:51 | 
| 2      | 15    |  1      | 'SewingButtons'     | 2        | 'Worf is in the air'| 2016-02-11 22:57:35 |
+--------+-------+---------+---------------------+----------+---------------------+---------------------+

How can I achieve this?

  • Improper use of GROUP BY; see the tag I added. – Rick James Oct 1 '17 at 14:19
  • @RickJames Could you please explain? – NineCattoRules Oct 1 '17 at 15:48
  • See here! Yet another reason why MySQL should not be used for serious work! – Vérace Oct 1 '17 at 16:51
  • @NineCattoRules: ANSI std says all non-aggregate columns from the select list should show up in the group by clause and vice versa (ie, all columns in group by clause should show up as non-aggregates in the select list); failure to follow the ANSI std leaves you at the mercy of the RDBMS and how it chooses to process a non-ANSI group by ... usually leading to undesired results – markp Oct 1 '17 at 17:53
  • @Vérace - a partial fix: As of 5.7.5, ONLY_FULL_GROUP_BY is enabled by default. – Rick James Oct 1 '17 at 17:55
3

Setup the tables and some sample data:

drop table if exists users;
drop table if exists views;
drop table if exists tracks;

create table users
(idu      int
,username varchar(30));

create table views
(id       int
,`by`     int
,track    int);

create table tracks
(id       int
,uid      int
,title    varchar(30)
,time     datetime);

insert into users values
(567,'TrainingPuppy'),
(687,'BathroomMakeover'),
( 45,'PoshNachos'),
( 15,'SewingButtons');

insert into views values
(1,  5, 55),
(2,  5, 55),
(3, 67, 55),
(4,  6, 29),
(5,125,  2),
(6,  5,698),
(7,  5,698);

insert into tracks values
(  2, 15,'Worf is in the air','2016-02-11 22:57:35'),
( 29,567,'Stargold'          ,'2016-08-11 22:57:28'),
( 55,567,'No love liers'     ,'2016-10-11 22:57:51'),
(698,567,'Lofe'              ,'2016-11-11 22:57:44');

Start with a (ANSI standard group by) query to obtain our counts:

select tracks.id,
       tracks.uid,
       count(*) as ucount,
       users.username,
       views.track,
       tracks.title,
       tracks.time

from   tracks
join   views
on     views.track = tracks.id

join   users
on     users.idu = tracks.uid

group by tracks.id,
         tracks.uid,
         users.username,
         views.track,
         tracks.title,
         tracks.time

We'll call the above 'user_counts', and create a query that generates a ranking over username ordered by ucount desc:

select id,
       uid,
       ucount,
       username,
       track,
       title,
       time,
       @user_rank := if(@current_username = username, @user_rank:=@user_rank+1, 1) as user_rank,
       @current_username := username

from   user_counts,

        -- initialize our variables
join   (select @user_rank := 0, @current_username := 'abcdefg') r

order by username, ucount desc

Then wrap this in our final select, making sure to only display those rows with a ranking of '1' ... pulling it all together into a single query we have:

select id,
       uid,
       ucount as 'count',
       username,
       track,
       title,
       time

from   (select id,
               uid,
               ucount,
               username,
               track,
               title,
               time,
               @user_rank        := if(@current_username = username, @user_rank:=@user_rank+1, 1) as user_rank,
               @current_username := username

        from   (select tracks.id,
                       tracks.uid,
                       count(*) as ucount,
                       users.username,
                       views.track,
                       tracks.title,
                       tracks.time

                from    tracks
                join    views
                on      views.track = tracks.id

                join    users
                on      users.idu = tracks.uid

                group by tracks.id,
                         tracks.uid,
                         users.username,
                         views.track,
                         tracks.title,
                         tracks.time) user_counts

                -- initialize our variables
        join   (select @user_rank := 0, @current_username := 'abcdefg') r

        order by username, ucount desc) dt

where   user_rank = 1

order by ucount desc, username

And the results:

id uid count username      track title              time
-- --- ----- ------------- ----- ------------------ -------------------
55 567     3 TrainingPuppy    55 No love liers      2016-10-11 22:57:51
 2  15     1 SewingButtons     2 Worf is in the air 2016-02-11 22:57:35

Some fiddles:


NOTE: If a given username has the same max(count) for more than one track/title, this solution will only display one record; to display multiple records would require a tweak to ensure said records receive the same ranking (ie, user_rank = 1).

  • Thanks, this is brilliant...however I need to DESC LIMIT 0, 20 the ucount since I'm using an Ajax call request system with infinite scroll down. – NineCattoRules Oct 2 '17 at 8:33
  • ok, so add your limit 0,20 to the final query ... yes? – markp Oct 2 '17 at 13:42
  • It's also very nice that I can get how many records needed of each artist just using something like user_rank < 3 (max 2 tracks of the same artist) – NineCattoRules Oct 2 '17 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.