SQL语句中的SUM函数用于diffentent where子句 [英] SUM Function in SQL Statement for diffentent where clauses

问题描述

嗨！



我正在使用SQL Server 2008数据库。



我告诉你一个有数据的表然后我告诉你我想要的结果然后建议我最好的解决方案。



表名tbl_Inventory

以下显示表中的数据。

Hi !

I am working on SQL Server 2008 Database.

I show you a table with data and then i show you what a result i want then suggest me the best solution.

Table Name tbl_Inventory
following shows the data in table.

Tansaction	ItemName	Quantity	Status
In on 1/1/2015	Square Pipe	15	Ordered
In on 1/1/2015	Square Pipe	10	Ordered
In on 1/1/2015	Square Pipe	20	Ordered
In on 1/1/2015	Square Pipe	5	Loaded
In on 1/1/2015	Square Pipe	12	Loaded
In on 1/1/2015	Square Pipe	8	Loaded
In on 1/1/2015	Square Pipe	16	Shipped
In on 1/1/2015	Square Pipe	20	Shipped
In on 1/1/2015	Square Pipe	7	Shipped
In on 1/1/2015	Square Pipe	25	Billed
In on 1/1/2015	Square Pipe	9	Billed
In on 1/1/2015	Square Pipe	11	Billed
In on 1/1/2015	Round Pipe	40	Ordered
In on 1/1/2015	Round Pipe	10	Loaded
In on 1/1/2015	Round Pipe	24	Loaded
In on 1/1/2015	Round Pipe	16	Loaded
In on 1/1/2015	Round Pipe	32	Shipped
In on 1/1/2015	Round Pipe	40	Shipped
In on 1/1/2015	Round Pipe	14	Shipped
In on 1/1/2015	Round Pipe	50	Billed
In on 1/1/2015	Round Pipe	18	Billed
In on 1/1/2015	Round Pipe	22	Billed

现在我想得到以下结果

Now i want the following result

ItemName      OrderedQty   LoadedQuantity   ShippedQuantity   BilledQuantity
SquarePipe        45             25               43              45  
RoundPipe         90             50               86              90

i希望你能理解虽然表格格式有问题，但我认为基本概念很明确我想要的东西。



请帮帮我


谢谢

i hope you will understand although there is some problem in table format but i think the basic concept is clear what i want.

please help me

thanks

推荐答案

一种方法是......

One way to do this is...

SELECT ItemName
, SUM(CASE [Status] WHEN 'Ordered' THEN Quantity ELSE 0 END) as OrderedQty 
, SUM(CASE [Status] WHEN 'Loaded' THEN Quantity ELSE 0 END) as LoadedQuantity 
, SUM(CASE [Status] WHEN 'Shipped' THEN Quantity ELSE 0 END) as ShippedQuantity 
, SUM(CASE [Status] WHEN 'Billed' THEN Quantity ELSE 0 END) as  BilledQuantity
FROM tbl_Inventory
GROUP BY ItemName

你所追求的是一个PIVOT。查询可能类似于：

What you're after is called a PIVOT. The query could look something like:

select *
from (select itemname, quantity, status from tablename) a
pivot (
   sum(quantity)
   for status in (['Ordered'], ['Loaded'],['Shipped'],['Billed']) 
) as amount

有关详细信息，请参阅 https://technet.microsoft.com/en-us/library/ms177410(v = sql.105）.aspx [ ^ ]

这篇关于SQL语句中的SUM函数用于diffentent where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！