SQL查询和行总数。

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

我有测试链接，可以尝试一些不同的查询。http://www.sqlfiddle.com/ ! 2 / c33cee / 1/0

2 个解决方案

#1

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

看到这个SQLFiddle

#2

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

这是SQLFiddle演示

#1