CODEWITHSUNDEEP
sqlms-access
user3569095
user3569095

Reputation: 1

Access sql query for most common product per customer

I've been trawling the net to try and find an answer to this and keep stumbling!

I've got the following equivalent table:

City | Colour
Manchester | Red
Manchester | Red
Manchester | Blue
Liverpool | Red
Liverpool | Red
Liverpool | Orange
Liverpool | Orange
Liverpool | Orange

and I'm trying to get some sql together (for MS Access) that will output the most common colour for each city, i.e. the above would output to:

Manchester | Red
Liverpool | Orange

can anyone help!?

Upvotes: 0

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This is a pain in MS Access, but you can do this with group by and having:

select city, colour
from t as t
group by city, colour
having count(*) = (select top 1 count(*)
                   from t as t2
                   where t2.city = t.city
                   group by t2.colour
                   order by count(*) desc, t2.colour
                  );

Upvotes: 0

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

Try something like this:

SELECT DISTINCT City, Colour
FROM t
WHERE Colour = (SELECT TOP 1 Colour
                FROM t As t2
                WHERE City = t.City
                GROUP BY Colour
                ORDER BY COUNT(*) DESC, Colour
               );

Upvotes: 1

Related Questions