CODEWITHSUNDEEP
mysqlsqldatabaseselectjoin
user2712040
user2712040

Reputation: 39

SQL Query Sum and total of rows

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

Answers (2)

Himanshu
Himanshu

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

peterm
peterm

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

Related Questions