CODEWITHSUNDEEP
sqloracle-database
Ankit
Ankit

Reputation: 17

SQL counting in a group by

I have a table like the following

C1 | C2 | C3

aa | bb | cc
aa | dd | tt
jj | uu | yy
aa | bb | cc
jj | rr | pp

C1 and C2 may may apear in any combination and may repeat values. Each C3 value is unique to a C2 value. I need to query the table to get all different values in C1 and no of times the map to different values in C2. in descending order(descending order isn't necessary). But the need to be grouped by C1 like follows

C1 | C2 |count| C3

aa | bb | 2   | cc
aa | dd | 1   | tt
jj | uu | 1   | yy
jj | rr | 1   | pp

Whats the best way to do this?

Upvotes: 0

Views: 99

Answers (2)

Taryn
Taryn

Reputation: 247620

This can be done using an aggregate function with a GROUP BY on the C1, C2, and C3 columns. Since you group by these 3 columns, the count will be a total of the rows that match:

select C1, C2, count(*) Total, C3
from yourtable
group by C1, C2, C3
order by C1, total desc

See SQL Fiddle with Demo

Upvotes: 2

JBrooks
JBrooks

Reputation: 10013

select C1, C2, Count(1), C3
from mytable
group by C1, C2, C3
order by 3 desc

Upvotes: 0

Related Questions