CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008sqlite
user2293838
user2293838

Reputation: 21

Group/Sort SQL by two columns or more. Most common values

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

Headers: 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

Headers: 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)

Hope this is clear. Many thanks

Upvotes: 0

Views: 56

Answers (1)

JNevill
JNevill

Reputation: 50218

You're getting close:

SELECT Part, Location, PartDesc, count(*) as Occurrence
FROM yourtable
GROUP BY Part, Location, PartDesc
ORDER BY Occurence

You want to put every field that you are grouping on, in the GROUP BY section. Really this is any field that you aren't aggregating with a Max(), Sum() or something like that. The ORDER BY can also take multiple fields if you want a more complex sort.

Upvotes: 1

Related Questions