Help with SQL View involving SUM function

Question

I would like to get 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?

Answer 1

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

Answer 2

To cover all OrderItemTypeId...

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

Answer 3

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.