Select unique count using two columns

Question

I have a table with two columns of interest: apc and tid. The same apc can be in a row with multiple tids, and an apc - tid pair can appear multiple times in the database (with different timestamps).

I want to get the apcs with a count of how many unique tids they match with. So for:

apc        tid
---------------
abc        012
abc        012
abc        322
def        322
def        432
def        000

I want to get:

apc        count
-----------------
abc          2
def          3

I tried doing a group by onapc and tid, but I get all of the duplicates as unique counts (so 3 and 3 above). I tried throwing in distinct in a few places, but that didn't work either.

This is a basic aggregation query with count(distinct).

Gordon Linoff
– Gordon Linoff

2014-06-12 19:23:58 +00:00
Commented Jun 12, 2014 at 19:23 — Gordon Linoff
– Gordon Linoff, Commented Jun 12, 2014 at 19:23

Siyual · Accepted Answer · 2014-06-12 19:24:34Z

1

You need to do a Count(Distinct tid):

Select  apc, Count(Distinct tid) as Count
From    Table
Group by apc

answered Jun 12, 2014 at 19:24

Siyual

17k8 gold badges47 silver badges60 bronze badges

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

Collectives™ on Stack Overflow

Select unique count using two columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related