1

Counting the rows in a SQL Group By query

 1 year ago
source link: https://akrabat.com/counting-the-rows-in-a-sql-group-by-query/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

Counting the rows in a SQL Group By query

I recently needed count the number of rows in an SQL query that had a Group By clause. It looked something like this:

SELECT account_name 
FROM events
WHERE created_at >= CURDATE() - INTERVAL 3 MONTH
GROUP BY account_id

This provides a list of account names (28 in my case), but if you try to count them using:

SELECT COUNT(account_name) as c 
FROM events
WHERE created_at >= CURDATE() - INTERVAL 3 MONTH
GROUP BY account_id

You get a list where each row is the count of how many events there are for each account, which is not what I want.

To get the number of accounts, use a sub-select:

SELECT COUNT(account_name) as c
FROM (
    SELECT account_name FROM events
    WHERE created_at >= CURDATE() - INTERVAL 3 MONTH
    GROUP BY account_id
) AS account_names

This give a single row which is the count of the number of rows returned by the sub-select.

As I don’t use sub-selects often, it took me a couple of goes to get this right as I initially forgot the final AS account_names which is needed to name the sub-select as a table which can be selected from.

Simple, once I got it working!

This article was posted on 19 April 2023 in Database


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK