如何获得总列数的总和

问题描述：

大家好

以下是我的桌子

和数据

Hi all

Below is my table
and data

CREATE TABLE car_uploads(
  location int(3),          -- Location ID
  item varchar(12),         -- Part ID
  quantity int(4)           -- Qty uploaded
);

INSERT INTO car_uploads (location, item, quantity) VALUES(001,'RED CAR',1);
INSERT INTO car_uploads (location, item, quantity) VALUES(002,'RED CAR',3);
INSERT INTO car_uploads (location, item, quantity) VALUES(003,'BLUE CAR',5);
INSERT INTO car_uploads (location, item, quantity) VALUES(002,'BLUE CAR',2);
INSERT INTO car_uploads (location, item, quantity) VALUES(001,'RED CAR',2);
INSERT INTO car_uploads (location, item, quantity) VALUES(002,'RED CAR',5);

以下是我得到数量总和列的代码

below is the code where i am getting the sum of quantity column

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,total

现在我想要总和总数（即总和（数量）为格兰特。

请告诉我如何获得..

now i want the sum of the total (i.e. sum(quantity) as grandtotal.

please tell how to get that..

答

使用组通过汇总

这里是sqlfiddel的例子

http://sqlfiddle.com/#!3/ae5db/6 [ ^ ]

这是给你的..干杯...... !!

Use Group by with rollup

here is example on sqlfiddel

http://sqlfiddle.com/#!3/ae5db/6[^]

This is for you .. Cheers...!!

SELECT item,
SUM(CASE WHEN location = 1 THEN quantity ELSE 0 END) as location_001,
SUM(CASE WHEN location = 2 THEN quantity ELSE 0 END) as location_002,
SUM(CASE WHEN location = 3 THEN quantity ELSE 0 END) as location_003,
SUM(quantity) total
  FROM car_uploads

GROUP BY item WITH ROLLUP

您可以在语句后使用COMPUTE，例如 COMPUTE（SUM（Quantity））之后你的小组一行

你也可以把它嵌入另一个查询中，例如

You can use COMPUTE after your statement eg COMPUTE(SUM(Quantity)) after your group by line

You can also nest this inside another query eg

Select sum(Quantity) from(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,total) X

相关推荐