I have a database table like this:
item_id sub_item_id
======= ===========
1 11
1 12
1 13
2 21
2 21 -- repeat
2 22
2 23
2 24
2 25
3 31
3 31 -- repeat
Is it possible to write a query that outputs 2 counts: The count of distinct item_ids and a count of distinct sub_item_ids across all item_ids. In summary, if that query is run against the sample data above, it would produce 3, 9
as output.
I can certainly do this like below but it will produce a verbose output and I will have to then run that data through a spreadsheet to get distinct count of item_ids:
SELECT item_id, COUNT(DISTINCT(sub_item_id))
FROM my_table
GROUP BY 1
4
Answers
You can use a subquery to get the distinct combinations of item_id and sub_item_id. Then, the outer query counts the distinct item_id and the distinct sub_item_id across all the results.
You can use two subqueries for get two counts
Just put two
COUNT(DISTINCT...)
calls in the SELECT list.DEMO
See example, pointed in my comment