Reputation: 39
I have a table that has three columns:
LOCATION | ITEM | QUANTITY
--------------------------------
001 RED CAR 1
002 RED CAR 3
003 BLUE CAR 5
002 BLUE CAR 2
001 RED CAR 2
002 RED CAR 5
Im trying to run a query that will tell me how many of each unique item each
location has. The reason I have multiple lines for the same location is that
each location might have multiple people inputting the records at the same time.
The goal is to get the total number of items and the total that each location
entered.
ITEM | LOCATION 001 | LOCATION 002 | LOCATION 003 | TOTAL
--------------------------------------------------------------
RED CAR 3 8 0 11
BLUE CAR 0 2 5 7
I can not come up with a single SELECT query that will get me both the total
for each location and the total for each item. Im trying to complete this with
a single query rather than running two separate query requests. Any help
would be greatly appreciated.
I have test link to try out some different queries on.
http://www.sqlfiddle.com/#!2/c33cee/1/0
Upvotes: 0
Views: 2219
Reputation: 32622
Try this query:
SELECT ITEM
,SUM(CASE WHEN LOCATION = 001 THEN QUANTITY ELSE 0 END) AS Location_001
,SUM(CASE WHEN LOCATION = 002 THEN QUANTITY ELSE 0 END) AS Location_002
,SUM(CASE WHEN LOCATION = 003 THEN QUANTITY ELSE 0 END) AS Location_003
,SUM(Quantity) AS Total
FROM Table1
GROUP BY ITEM;
In case if you don't know Locations, you can try this dynamic query:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN `LOCATION` = ''',
`LOCATION`,
''' THEN QUANTITY ELSE 0 END) AS `',
`LOCATION`, '`'
)
) INTO @sql
FROM Table1;
SET @sql = CONCAT('SELECT ITEM, ', @sql,'
,SUM(Quantity) AS Total
FROM Table1
GROUP BY ITEM
');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Result:
| ITEM | 1 | 2 | 3 | TOTAL |
|----------|---|---|---|-------|
| BLUE CAR | 0 | 2 | 5 | 7 |
| RED CAR | 3 | 8 | 0 | 11 |
See this SQLFiddle
Upvotes: 1
Reputation: 92845
If all locations are known before hand you can do
SELECT item,
SUM(CASE WHEN location = 1 THEN quantity ELSE 0 END) location_001,
SUM(CASE WHEN location = 2 THEN quantity ELSE 0 END) location_002,
SUM(CASE WHEN location = 3 THEN quantity ELSE 0 END) location_003,
SUM(quantity) total
FROM car_uploads
GROUP BY item
Output:
| ITEM | LOCATION_001 | LOCATION_002 | LOCATION_003 | TOTAL | -----------|--------------|--------------|--------------|-------| | BLUE CAR | 0 | 2 | 5 | 7 | | RED CAR | 3 | 8 | 0 | 11 |
Here is SQLFiddle demo
Upvotes: 1