SELECT MAX from COUNT, other row variables are not correct

Question

I am trying to select the MAX of a COUNT grouping them by state (So one max for each distinct value in state). The count function works as intended.

SELECT c.id, c.name, t.name as type, COUNT(*) as count, c.state 
FROM bookings_facilities f 
JOIN bookings b 
ON b.id = f.booking_id 
JOIN clients c 
ON c.id = b.client_id 
JOIN client_types t 
ON c.type = t.id 
WHERE t.name = "School" 
GROUP BY c.id

Here is the results,

I use the SQL statement below to try and choose the MAX of count grouping them by state.

SELECT *, MAX(z.count) 
FROM (SELECT c.id, c.name, t.name as type, COUNT(*) as count, c.state 
    FROM bookings_facilities f 
    JOIN bookings b 
    ON b.id = f.booking_id 
    JOIN clients c 
    ON c.id = b.client_id 
    JOIN client_types t 
    ON c.type = t.id 
    WHERE t.name = "School" 
    GROUP BY c.id) z
GROUP BY z.state

Here is the results,

The 3 states, which appear only once in result 1 seems to be fine, but for the state, Selangor, which appears twice in the first result, had some problems.

The SQL query selected the right MAX(Count) which is 6, but instead of returning id as 1027 it returned id as 1002 which only has count as 1 in the first result.

I have tried it with different sets of data but I can't seem to get the details of the actual MAX(Count) row.

Here is the database design for reference

SQL Fiddle

Expected outcome is this, (Just the second row output needs to be changed). Output Current Output Link

This is **nth-row-per-group** problem. If you search properly on Stack overflow, you can easily get 100s of examples of the same. Few more things: Your `group by` is not valid SQL, and in newer versions of MySQL, it would fail. Secondly, you dont need to first group by on `id` and then do a second group by using subquery. Simply do a direct group by on `state` — Madhur Bhaiya, Nov 04 '18 at 16:26
@MadhurBhaiya I didn't know what the problem was called. I am searching now. I tried using a single group by instead of two but the thing is I want to find the most facilities booked by clients depending on their state. The first group by groups count and the second one groups them by state. I don't get the results I want if I combine them. — kks21199, Nov 04 '18 at 16:40
**Help us** if you want help :) Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308) — Madhur Bhaiya, Nov 04 '18 at 16:43
I have set up a sql fiddle. I have updated the post with my expected outcome. Here: http://sqlfiddle.com/#!9/8f68e3 — kks21199, Nov 04 '18 at 16:59
Do you have access to MySQL version 8.0 ? Or, can you upgrade ? Solution for older version will be extremely verbose. — Madhur Bhaiya, Nov 04 '18 at 17:38

Madhur Bhaiya · Accepted Answer · 2018-11-04T18:03:05.603

Since you can use MySQL 8.0, we can solve your problem using Window Functions. Over a partition of state, we will determine Row_Number() with the row having highest count as row number 1 and so on. Now, we simply need to consider only those rows where row number is 1, for a particular state.

Additionally, in your attempt, GROUP BY was not valid SQL. Older versions of MySQL were lenient and allowed it; but newer versions don't, unless you turn off the strict only_full_group_by mode. However, you should not disable it, and instead fix the query. The basic gist is that when using a Group By, your Select clause should only contain aggregated column(s)/expression(s) and/or the column(s)/expression(s) defined in the Group By clause. Do read: Error related to only_full_group_by when executing a query in MySql

 SELECT dt2.id,
       dt2.NAME,
       dt2.state,
       dt2.type,
       dt2.count
FROM   (SELECT dt1.id,
               dt1.NAME,
               dt1.state,
               dt1.type,
               dt1.count,
               Row_number()
                 OVER (
                   partition BY dt1.state
                   ORDER BY dt1.count DESC) AS row_num
        FROM   (SELECT c.id,
                       c.NAME,
                       c.state,
                       t.NAME   AS type,
                       Count(*) AS count
                FROM   bookings_facilities AS f
                       JOIN bookings AS b
                         ON b.id = f.booking_id
                       JOIN clients AS c
                         ON c.id = b.client_id
                       JOIN client_types AS t
                         ON c.type = t.id
                WHERE  t.NAME = 'School'
                GROUP  BY c.id,
                          c.NAME,
                          c.state,
                          type) AS dt1) AS dt2
WHERE  dt2.row_num = 1

Result:

| id   | NAME                                     | state     | type   | count |
| ---- | ---------------------------------------- | --------- | ------ | ----- |
| 1006 | Holy Child Montessory School Of Fairview | Manila    | School | 1     |
| 1027 | Sri Kuala Lumpur                         | Selangor  | School | 6     |
| 1010 | Singapore American School                | Singapore | School | 1     |
| 1015 | Keika Junior & Senior High School        | Tokyo     | School | 1     |

View on DB Fiddle

SELECT MAX from COUNT, other row variables are not correct

1 Answers1