如何获得总列数的总和 [英] how to get Sum of total column

问题描述

大家好



以下是我的桌子

和数据

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 [ ^ ]



这是给你的..干杯...... !! < br $>

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））之后你的小组一行



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

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

这篇关于如何获得总列数的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！