CODEWITHSUNDEEP
mysqlsql
user2531590
user2531590

Reputation:

SQL statement GROUP BY

I am trying to do the best seller system but I am having some problem on the SQL statement. Here is my SQL:

select sp.name, pr.productName, op.productID, SUM(op.orderQuantity) AS totalQty from sm_orderedproduct op
INNER JOIN sm_payment p ON op.orderID = p.orderID 
INNER JOIN sm_product pr ON op.productID = pr.productID
INNER JOIN sm_sellerproduct sp ON sp.productID = pr.productID
WHERE MONTH(str_to_date( dateOfPurchase, '%d/%m/%Y' )) = 7
GROUP BY sp.name, pr.productName, op.productID
ORDER BY SUM(op.orderQuantity) DESC LIMIT 4;

With this SQL statement, I get duplicate seller name. For example

Seller Dean sold 97 TV

Seller Rk sold 20 Desktop

Seller Dean again sold 15 harddisks

Seller Gab sold 2 wooden tables

As you can see, seller Dean is repeated twice because of different products sold. I want to do like:

Seller Dean sold 112 products

Seller Rk sold 20 products

Seller Gab sold 2 products

And whatever seller sold ? products

I believe that there's something went wrong with my group by clause. Thanks in advance.

Upvotes: 1

Views: 66

Answers (1)

sgeddes
sgeddes

Reputation: 62831

You need to remove the product related information from the query/group by:

select sp.name, sum(op.orderQuantity) AS totalQty 
from sm_orderedproduct op
    inner join sm_payment p ON op.orderID = p.orderID 
    inner join sm_product pr ON op.productID = pr.productID
    inner join sm_sellerproduct sp ON sp.productID = pr.productID
where MONTH(str_to_date( dateOfPurchase, '%d/%m/%Y' )) = 7
group by sp.name
order by sum(op.orderQuantity) desc 
limit 4;

Upvotes: 3

Related Questions