CODEWITHSUNDEEP
sql
personaelit
personaelit

Reputation: 1653

Help with SQL View involving SUM function

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?

Upvotes: 2

Views: 2187

Answers (3)

anthares
anthares

Reputation: 11223

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

Upvotes: 3

AdaTheDev
AdaTheDev

Reputation: 147244

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.

Upvotes: 0

kevchadders
kevchadders

Reputation: 8335

To cover all OrderItemTypeId...

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

Upvotes: 1

Related Questions