yanman1234 yanman1234 - 5 months ago 10
MySQL Question

MySQL use DISTINCT with GROUP BY on same column

I currently have a query similar to:

select customers.customerId, items.itemName, sum(orders.quantity) as boughtTotal
from customers join orders on customers.customerId = orders.customerId
join items on items.itemId = orders.itemId
group by customers.customerId, items.itemName
order by boughtTotal desc;


The purpose of the query is to relate customers to items they bought, which may span over many orders, and total the amount of each unique item bought. This being achieved with what I have. I want to take this a step further now and select the most popular item for each customer. Since this is ordered with most bought items at the top, I figured I'd just have to add
distinct
next to
customers.customerId
in the
select
statement to have duplicates removed. However, adding
distinct
seems to do nothing. I'd appreciate help in knowing why
distinct
is seemingly doing nothing here, but also how to achieve what I'm trying to do - remove duplicates besides a customer's most popular item.

Tables:

customers
customerId | name
1 | John
2 | Jane

orders
orderId | customerId | quantity | itemId
1 | 1 | 11 | 1
2 | 2 | 13 | 2
3 | 1 | 4 | 2
4 | 2 | 14 | 1
5 | 1 | 1 | 1

items
itemId | itemName
1 | dog
2 | cat


So from this data the current query will return the following:

customerId | itemName | boughtTotal
2 | dog | 14
2 | cat | 13
1 | dog | 12
1 | cat | 4


And what I'd like to have is the following:

customerId | itemName | boughtTotal
2 | dog | 14
1 | dog | 12

Answer

Try this;)

select t1.*
from (
    select customers.customerId, items.itemName, sum(orders.quantity) as boughtTotal 
    from customers join orders on customers.customerId = orders.customerId 
    join items on items.itemId = orders.itemId
    group by customers.customerId, items.itemName) t1
inner join (
    select max(boughtTotal) as boughtTotal, customerId
    from (
        select customers.customerId, items.itemName, sum(orders.quantity) as boughtTotal 
        from customers join orders on customers.customerId = orders.customerId 
        join items on items.itemId = orders.itemId
        group by customers.customerId, items.itemName)t
    group by customerId) t2 on t1.customerId = t2.customerId and t1.boughtTotal = t2.boughtTotal 

DEMO HERE

Comments