SQL: Count distinct row values in table

Question

I have a similar table to this in SQL:

id |tag | entryID
---+----+--------
1  |foo | 0
2  |foo | 0
3  |bar | 3
5  |bar | 3
6  |foo | 3
7  |foo | 3

I want to run a query to count distinct rows in the table (with the id column dropped). The result should look like this (or a transpose of this table):

(tag=foo, entryID=0) | (tag=foo, entryID=3) | (tag=bar, entryID=3)
---------------------+----------------------+---------------------
2                    | 2                    | 2

What should this query look like?

Note: The values in each of the columns are not known beforehand.

Have you tried anything at all that you can show us to get us started, or are you asking for a quotation for the work — RiggsFolly, Aug 10 '16 at 00:45
Dou know the values for tag and entry beforehand? If so, you can use `conditional aggregation`. If not, you'll need to combine that approach with `dynamic sql`. — sgeddes, Aug 10 '16 at 00:46
Similar to: http://stackoverflow.com/questions/5737628/mysql-count-distinct — Mike Robinson, Aug 10 '16 at 01:34

score 2 · Accepted Answer · answered Aug 10 '16 at 00:45

You can do this using conditional aggregation:

select sum(tag = 'foo' and entryId = 0) as "tag=foo, entryID=0",
       sum(tag = 'foo' and entryId = 3) as "tag=foo, entryID=3",
       sum(tag = 'bar' and entryId = 3) as "tag=bar, entryID=0"
from t;

However, the normal method is to put the counts in rows, not columns:

select tag, entryId, count(*)
from t
group by tag, entryId;

The rows are much more versatile, because you don't have to list out every combination you might want to match.

SQL: Count distinct row values in table

1 Answers1