SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Two Different Totals
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

armesjr84
Starting Member

1 Posts

Posted - 05/06/2013 :  17:08:37  Show Profile  Reply with Quote
I have a table that has Procedure, Hospital, City_State, and a Volume_Flag (Volume >5 = '1', volume <6 = '0') column. I am having issues writing a query that will provide me with the volumes when the volume flag = 1 or when the volume flag = 0. Thanks in advance for any help.



Table:
Procedure Hospital City_State Volume Volume_Flag
A Hospital A Richmond, VA 5 1
A Hospital B Richmond, VA 7 1
A Hospital C Midlothian, Va 5 0
B Hospital A Richmond, VA 34 1
B Hospital B Richmond, VA 27 1
B Hospital C Midlothian, Va 5 0
C Hospital A Richmond, VA 2 1
C Hospital B Richmond, VA 9 1
C Hospital C Midlothian, Va 25 1

Desired Results:

Procedure City, State Total Vol VF =1 Total Vol VF = 0
Procedure A Richmond, VA 7 5
Procedure A Midlothian, VA 0 5
Procedure B Richmond, VA 61 0
Procedure B Midlothian, VA 0 5
Procedure C Richmond, VA 9 2
Procedure C Midlothian, VA 25 0



Edited by - armesjr84 on 05/06/2013 17:15:34

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 05/06/2013 :  17:48:23  Show Profile  Reply with Quote
SELECT
	[Procedure],
	City,
	STATE,
	SUM(CASE WHEN Volume_Flag = 1 THEN Volume ELSE 0 END) AS [Total Vol VF = 1],
	SUM(CASE WHEN Volume_Flag = 0 THEN Volume ELSE 0 END) AS [Total Vol VF = 0]
FROM
	TABLE
GROUP BY
	[Procedure],
	City,
	STATE
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/07/2013 :  00:32:15  Show Profile  Reply with Quote
sorry your sample data doesnt make much sense. Can you expplain these lines?


Procedure A Richmond, VA 7 5

Procedure C Richmond, VA 9 2


both the records for these two procedures have Volume_Flagn as 1 so didnt understand how you will show one under 1 and other under 0 for VF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000