hlin117 hlin117 - 6 months ago 22
MySQL Question

SQL: Count distinct row values in table

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
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.


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.