CODEWITHSUNDEEP
sqlsqlite
Mark
Mark

Reputation: 53

SQLite SELECT with JOIN and GROUP?

I'm trying to add a column based on the result of the following string:

SELECT product_code AS "Product Code", SUM(quantity) AS "Quantity" 
FROM receipts_items
GROUP BY product_code
ORDER BY SUM(quantity) DESC

this string gives me a Product Code and the quantity sold. What I need to add is, based on the product code, the description of that product code.

product_code and descriptions column are in the same table "products" quantity is in another table "receipts_items"

Thanks!!!

Upvotes: 1

Views: 5980

Answers (1)

Steven Moseley
Steven Moseley

Reputation: 16325

You want to JOIN the two tables on product_code.

You really ought to do a LEFT JOIN to catch products with no associated receipt items, then put a CASE statement in your SUM to reduce NULL values to 0.

SELECT p.product_code AS "Product Code",
    p.description AS "Description", 
    SUM(CASE WHEN ri.quantity IS NULL THEN 0 ELSE ri.quantity END) AS "Quantity" 
FROM products AS p
    LEFT JOIN receipts_items AS ri ON ri.product_code = p.product_code
GROUP BY p.product_code
ORDER BY SUM(ri.quantity) DESC

Upvotes: 4

Related Questions