CODEWITHSUNDEEP
mysqlsqlmysql-8.0
villapx
villapx

Reputation: 1873

MAX() and GROUP BY, need COUNT(*) of whole result

I have a SELECT query I'm executing which has a MAX() column and a GROUP BY clause, and in addition to the results of this query that I need to return to the client, I also need to return the total count of all results as well.

Essentially my query is this:

SELECT unique_id, col1, col2, MAX(col3) as col3
FROM tbl
GROUP BY col1, col2

It'll usually have a WHERE clause too.

The unique_id is the primary key of the table.

When returning this data to the client, I also specify LIMIT and OFFSET clauses to limit the number of results being retrieved at one time. My problem is that I also need to display the total count of results that the above query would produce if it didn't have the LIMIT and OFFSET clauses, so that the client can later/incrementally retrieve the rest.

I know I can easily use a WITH temporary table to get what I want:

WITH temp AS (
    SELECT unique_id, col1, col2, MAX(col3) as col3
    FROM tbl
    GROUP BY col1, col2
)
SELECT count(*) FROM temp

But I'm concerned about the efficiency of this. The sans-LIMIT-and-OFFSET query could return tens of thousands of rows, so I'm thinking that the WITH approach to getting the total count isn't the best way to do this.

Is there a more efficient way, which I'm not thinking of? Or is the WITH method the fine (e.g. is the MySQL server "smart" enough to not allocate the entire result set of the query to get the count)?

Example data

Assume this is the data in my table:

unique_id  col1  col2  col3
___________________________
1          5     8     30
2          5     8     33
3          5     9     40
4          6     8     30
5          6     8     31
6          6     8     32
7          6     9     39
8          7     8     33
9          7     8     32
10         8     8     34

So my SELECT query would return this (assume the client specified LIMIT 4 OFFSET 0):

SELECT unique_id, col1, col2, max(col3) as col3
FROM tbl
GROUP BY col1, col2
LIMIT 4
OFFSET 0;
    unique_id  col1  col2  col3
    ___________________________
    2          5     8     33
    3          5     9     40
    6          6     8     32
    7          6     9     39

And then I'd use that query without the LIMIT and OFFSET clauses as a subquery and SELECT COUNT(*) from it, which would return 6, and I'd return both the 6 and the results to the client.

Upvotes: 1

Views: 369

Answers (1)

Andriy M
Andriy M

Reputation: 77657

MySQL 8 has introduced support for window functions, including window aggregate functions. Window aggregate functions allow you to return aggregated results alongside non-aggregated data. Basically, you can turn a regular aggregate function into a window aggregate function by attaching an OVER clause to it, but there are often additional options that you may need to specify, which is explained in detail in the linked manual.

You can use window aggregate functions in GROUP BY queries too. In those cases the window aggregate functions will apply to the row sets after the grouping is done. Note also that adding LIMIT will not affect the result of a window aggregate function.

Taking all the above into account, you can modify the original query like this:

SELECT
  unique_id,
  col1,
  col2,
  MAX(col3) as col3,
  COUNT(*) OVER () AS TotalRows
FROM
  tbl
GROUP BY
  col1,
  col2
LIMIT
  4 OFFSET 0
;

and get the original detail data together with the number of rows in one go. The OVER clause has no additional subclauses, which means it applies to the entire rowset.

As I have said, a window aggregate function will disregard the LIMIT clause, if one is attached to the query. Therefore, the TotalRows column above will reflect the number of rows as though the limit were not applied.

Upvotes: 4

Related Questions