mysql - Using Group By

Question

I am trying to write a mysql query for an app I'm developing for android.

I have a database that has a bill_content table and a products table I want to select top 10 most sold products.

This is a minimal version of what I have, but it's all I need to get an answer here.

bill_content table has the columns: id, id_product, quantity (id_product and quantity here can be duplicate because this table is larger, containing id_bill and other information)

products table has the columns: id, name

SELECT products.name AS Product,
       bill_content.quantity AS Quantity

FROM bill_content, products
WHERE bill_content.id = products.id
ORDER BY bill_content.quantity DESC
LIMIT 10

Of course this returns a table of 2 rows containing all the products and their quantity in the bill_content table, but there are duplicates and I need to make sum of their quantity and display them as a single row.

Thank you in advance.

ANSWERED This could be done using GROUP BY as Gordon Linoff said.

Gordon Linoff · Accepted Answer · 2015-01-04 18:04:27Z

3

You want a group by. You should also learn to use proper explicit join syntax:

SELECT p.name AS Product,
       SUM(bc.quantity) AS Quantity
FROM bill_content bc JOIN
     products p
     ON bc.id = p.id
GROUP BY p.name
ORDER BY SUM(bc.quantity) DESC
LIMIT 10;

answered Jan 4, 2015 at 18:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

laurGanta Over a year ago

Thank you, I used the JOIN syntax also. Will accept this when accepting becomes available.

Collectives™ on Stack Overflow

mysql - Using Group By

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related