Skip to content

Count from subquery if query uses HAVING or GROUP BY#1398

Merged
stephenafamo merged 1 commit intoaarondl:masterfrom
renom:master
Oct 23, 2024
Merged

Count from subquery if query uses HAVING or GROUP BY#1398
stephenafamo merged 1 commit intoaarondl:masterfrom
renom:master

Conversation

@renom
Copy link
Contributor

@renom renom commented Jul 8, 2024

This PR enables using statement SELECT COUNT(*) FROM (SELECT ...); if there's HAVING or GROUP BY in the query.

@stephenafamo
Copy link
Collaborator

Why is this necessary? Are the queries more performant?

@renom
Copy link
Contributor Author

renom commented Oct 23, 2024

It's necessary because aggregate functions are run for each group separately.
So if there's GROUP BY, COUNT(*) will count a number of grouped rows in each group (i.e. COUNT(*) will be calculated for each returned row).
But we need to return the count of returned rows.

@renom
Copy link
Contributor Author

renom commented Oct 23, 2024

https://www.w3schools.com/sql/trysql.asp?filename=trysql_editor
Try here the following queries:

SELECT Country, COUNT(*) FROM Customers GROUP BY Country;

vs

 SELECT COUNT(*) FROM (SELECT Country FROM Customers GROUP BY Country);

The first query returns COUNT(*) for each country, the second query returns COUNT(*) of countries.
The first query returns 21 rows, the second query return one row with value 21.

@stephenafamo
Copy link
Collaborator

Makes sense 👍🏾

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants