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.
精彩评论