CODEWITHSUNDEEP
sqlsql-serversql-server-2012
shenn
shenn

Reputation: 889

Get count of multiple column occurrences in SQL

Have a question about a query I am attempting to write, using SQL Server 2012. I have a table with 2 columns (Agent and UN) I am focusing on. I would like to find the number off occurrences that Agent and UN occur. For example, in the table below:

+----+-------+----+
| ID | Agent | UN |
+----+-------+----+
| 1  | 27    | 1  |
| 2  | 28    | 1  |
| 3  | 27    | 1  |
| 4  | 27    | 2  |
| 5  | 28    | 2  |
| 6  | 28    | 2  |
+----+-------+----+

An example would look similar to this:

+-------+--------+----+
| Count | Agent  | UN |
+-------+--------+----+
|     2 |     27 |  1 |
|     2 |     28 |  2 |
|     1 |     27 |  2 |
|     1 |     28 |  1 |
+-------+--------+----+

The ID does not matter, I just want to get where Agent and UN occur as pairs and their counts.

My attempt was this. I would have to do this for every agent and then combine them, but I'm thinking there is probably a quicker way to accomplish this? 

select count(UN) as Count, Agent, UN 
Where Agent = 27
group by UN
order by count(UN) desc

Upvotes: 1

Views: 1610

Answers (6)

potashin
potashin

Reputation: 44581

You should GROUP BY UN and Agent and then COUNT duplicates. To retrieve data for all possible values in Agent column you should remove your WHERE clause :

SELECT COUNT(*) as Count
     , Agent
     , UN 
FROM table
GROUP BY UN
       , Agent
ORDER BY COUNT(*) DESC

Upvotes: 2

user3634541
user3634541

Reputation: 1

It looks like you want is:

select count(UN) as Count, Agent, UN 
group by Agent, UN
order by count(UN) desc

This should give you what you are looking for.

Upvotes: 0

Lajos Arpad
Lajos Arpad

Reputation: 76454

This should fix the problem:

select count(*) as Count, Agent, UN 
from Agent
group by Agent, UN
order by count(*) desc

Upvotes: 0

acutesoftware
acutesoftware

Reputation: 1091

You need to group by both Agent and UN

 select count(*) as Count, Agent, UN 
 from tbl
 group by Agent, UN
 order by Count(*) desc

Upvotes: 1

Nicholas Carey
Nicholas Carey

Reputation: 74227

Try

select Frequency = count(*) ,
       Agent     = t.Agent  ,
       UN        = t.UN
from dbo.my_table t
group by t.Agent , t.UN
order by count(*) desc, t.Agent , t.UN

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

Group by both agent and UN, and take out the condition from the where clause, to get the count for all groups.

select      count(*), agent, un
from        tbl
group by    agent, un
order by    1 desc

Upvotes: 4

Related Questions