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
– RiggsFolly, Commented Aug 10, 2016 at 0: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
– sgeddes, Commented Aug 10, 2016 at 0:46
Similar to: stackoverflow.com/questions/5737628/mysql-count-distinct — Mike Robinson
– Mike Robinson, Commented Aug 10, 2016 at 1:34

Gordon Linoff · Accepted Answer · 2016-08-10 00:45:46Z

2

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.

answered Aug 10, 2016 at 0:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL: Count distinct row values in table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related