yaylitzis yaylitzis - 2 months ago 7
SQL Question

Return sum from many records

I have a table in

SQLite


CREATE TABLE "INVENTORY" {
"product" VARCHAR NOT NULL,
"version" VARCHAR NOT NULL,
"quantity" INTEGER,
//etc
}


and some records are like

"product" - "01" - 1000
"product" - "01" - 1000
"product" - "02" - 4000
"product" - "02" - 1000
"product" - "03" - 3000


I try to make a query so I get all the sums for each product and version. In the above example the query would returned

"product" - "01" - 2000
"product" - "02" - 5000
"product" - "03" - 3000


I tried this query which returns
ambiguous column name: product


SELECT product , version, SUM(quantity) AS total
from INVENTORY
INNER JOIN INVENTORY
ON product AND version
GROUP BY product, version

Answer
SELECT product, version, SUM(quantity) AS total 
FROM INVENTORY
GROUP BY product, version