distinct pairs plus the number of times each pair appears

Question

consider

create table pairs (number a, number b)

where the data is

1,1
1,1
1,1
2,4
2,4
3,2
3,2
5,1

what query gives me all unique pairs plus a column representing the number of times that pair appeared. ie:

1,1,3
5,1,2
2,4,2
3,2,1

Note: This is SO question Distinct pair of values SQL but with the added "count" column requirement

Is `(2,4)` different from `(4,2)`? – Clodoaldo Neto Jul 17 '17 at 15:32 — Clodoaldo Neto, Jul 17 '17 at 15:32
@ClodoaldoNeto yes, a=2,b=4 would be different than a=4,b=2 – code_monk Jul 17 '17 at 16:57 — code_monk, Jul 17 '17 at 16:57

score 2 · Accepted Answer · answered Jul 17 '17 at 15:18

2

select 
    a,b,count(*) c
from 
    pairs
group by 
    a,b

answered Jul 17 '17 at 15:18

Alex

instead of `count(*) c` do you mean `count(*) as c` ? – code_monk Jul 17 '17 at 17:01
Yes, this is T-SQL (SQL Server), maybe for other RDBMS you need the as. – Alex Jul 17 '17 at 19:06

1 Answers1