Grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping

Author

Topic

learzbu
Starting Member

27 Posts

Posted - 2011-11-15 : 10:45:28

I wanted to know if something like this would be possible:

Job_No ------------Product-----------Value

101----------------AC unit------------100
101----------------Fridge-------------150
102----------------BC Unit-------------75
102----------------Delivery------------50

What I want to do group the job numbers, if a job group contains delivery then give me the delivery charge. If not then give me a value of 0 for the delivery

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 10:47:56

do you mean this?


SELECT Job_No,
SUM(CASE WHEN Product='Delivery' THEN Value ELSE 0 END) AS DeliveryCharge
FROM table
GROUP BY Job_No

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-15 : 11:29:35

quote:
Originally posted by learzbu

I wanted to know if something like this would be possible:

Job_No ------------Product-----------Value

101----------------AC unit------------100
101----------------Fridge-------------150
102----------------BC Unit-------------75
102----------------Delivery------------50

What I want to do group the job numbers, if a job group contains delivery then give me the delivery charge. If not then give me a value of 0 for the delivery

Is that your source table or the output?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

learzbu
Starting Member

27 Posts

Posted - 2011-11-15 : 12:56:50

Lamprey -- that is an example of the source table I am working with. Visakh16's answer has gotten me the results that I want from :

(SELECT
SUM(CASE WHEN product like 'FREIGHT' THEN val ELSE 0 END) AS DeliveryCharge
FROM scheme.opsahistm join scheme.opheadm on
scheme.opsahistm.invoice like scheme.opheadm.invoice_no
GROUP BY invoice)

but i don't know how to work this in to my query as a column at the end of my results set

learzbu
Starting Member

27 Posts

Posted - 2011-11-15 : 15:10:41

The Below does get me the results I need:

SELECT invoice,
sum(CASE WHEN product like 'FREIGHT' THEN val ELSE 0 END) AS DeliveryCharge
FROM scheme.opsahistm GROUP BY invoice

But I need to tie this results set into another query, when I add in a join to this query to get more data it fails:

SELECT order_no, invoice,
sum(CASE WHEN product like 'FREIGHT' THEN val ELSE 0 END) AS DeliveryCharge
FROM scheme.opsahistm join scheme.opheadm on scheme.opsahistm.invoice like scheme.opheadm.invoice_no
GROUP BY invoice

Will the group by keyword not work accross a join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 00:54:03

[code]
SELECT o.order_no,
o.invoice,
t.DeliveryCharge
FROM
(
SELECT invoice,
sum(CASE WHEN product like 'FREIGHT' THEN val ELSE 0 END) AS DeliveryCharge
FROM scheme.opsahistm
GROUP BY invoice
)t
INNER JOIN scheme.opheadm o
on t.invoice like o.invoice_no
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learzbu
Starting Member

27 Posts

Posted - 2011-11-16 : 06:03:06

Thanks Again Visakh16, I was able to work this in and get exactly what I needed!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources