Group and then count distinct

Databases
1

I could do the following as several queries or a view and then a query but i would like to do as one query.

I have a table with 2 columns Type and LinkID - both contain a number. Type contains 1, 2 or 3 and LinkID contains a number reference which could link to the same record:

Type LinkID
1 4
1 4
1 4
1 6
2 3
2 3
2 3
3 4
3 5
3 6
3 7

What I want to do is count the number of distinct LinkIDs for each type, giving:

Type Count
1 2
2 1
3 4

how do I do this?

cheers

2 
SELECT type
     , COUNT(DISTINCT linkid) AS count
  FROM daTable
GROUP
    BY type

:cool:

p.s. why does your table have duplicate rows in the first place – that’s the real wtf here

3

Thanks - I think I must have tried every combo but that one :slight_smile:

p.s. the example is a massive simplification of the problem - my tables really aren’t that stupid (I hope!) :slight_smile: