CODEWITHSUNDEEP
sqlcountsubqueryaggregate-functions
Stijn Sanders
Stijn Sanders

Reputation: 36850

SQL: count of count in one query

Is there a straight-forward way in SQL syntax to write this in a single query without a sub-query? And is there any chance in a performance-gain that way?

SELECT ProductCount, COUNT(*) as Occurence
FROM (
    SELECT OrderID, COUNT(DISTINCT ProductID) as ProductCount
    FROM OrderLine
    GROUP BY OrderID
) X
GROUP BY ProductCount
ORDER BY ProductCount

Upvotes: 0

Views: 149

Answers (1)

Ben
Ben

Reputation: 35633

  1. What you have in your question is the recommended way to do it.

  2. You are unlikely to see any performance gain from any change. This is a very common pattern which your database should cope with just fine. If it is slow, consider adding appropriate indexes.

Upvotes: 1

Related Questions