开发者

Help with SQL View involving SUM function

I would like to 开发者_如何学Pythonget the following data (and more) into a single view.

SELECT Price FROM dbo.OrderItems WHERE OrderItemTypeId = 0 

And

SELECT SUM (Price) AS ShippingTotal FROM dbo.OrderItems WHERE OrderItemTypeId = 1

I can’t seem to figure out how to do this with my weak SQL skills. Anybody know how I could do this?


You can use UNION statement:

SELECT Price FROM dbo.OrderItems WHERE OrderItemTypeId = 0 

UNION

SELECT SUM (Price) AS ShippingTotal FROM dbo.OrderItems WHERE OrderItemTypeId = 1

But what is the semantic behind this ... In the first statement you have only one row with id = 0, in the second an aggregate function grouped by the same column (which suppose that there are more than one record with id=1). It will be helpful to show us the sample data for you table.

To improve your skills about UNION, see here: http://www.w3schools.com/sql/sql_union.asp


To cover all OrderItemTypeId...

SELECT OrderItemTypeId, SUM(Price) AS ShippingTotal
FROM dbo.OrderItems 
GROUP BY OrderItemTypeId 


One way would be like this:

SELECT Price, 0 AS OrderItemTypeId FROM dbo.OrderItems WHERE OrderItemTypeId = 0 
UNION ALL
SELECT SUM(Price) AS Price, 1 AS OrderItemTypeId FROM dbo.OrderItems 
WHERE OrderItemTypeId = 1

The 2nd column I've added to the results allows you to determine the different rows.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜