CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008count
user2293838
user2293838

Reputation: 21

Most frequent values by two columns SQL

I am trying to figure out the most occurring values within a table in groups. This is for SQL

Part | location | PartDesc
-----+----------+-------------
A    | 2        | Part A
A    | 2        | Part A
A    | 2        | Part A
A    | 1        | Part A
A    | 1        | Part A
B    | 1        | Part B
B    | 2        | Part B

So the output needs to show 

Part | Location | PartDesc | Occurrence 
-----+----------+----------+--------------
A    | 2        | Part A   | 3
A    | 1        | Part A   | 2 
B    | 1        | Part B   | 1
B    | 2        | Part B   | 1

So far I have

Select Part, count(*) as occurrence
from table1
group by Part
order by count(*desc)

Upvotes: 1

Views: 2612

Answers (1)

John Bell
John Bell

Reputation: 2350

SELECT 
    Part,
    Location,
    PartDesc,
    COUNT(*) AS Occurrence
FROM
    table1
GROUP BY 
    Part,
    Location,
    PartDesc
ORDER BY 
    Occurrence DESC

Thanks.

Upvotes: 4

Related Questions