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.
| Author |
Topic |
|
armesjr84
Starting Member
1 Post |
 Posted - 2013-05-06 : 17:08:37
|
| 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_FlagA Hospital A Richmond, VA 5 1A Hospital B Richmond, VA 7 1A Hospital C Midlothian, Va 5 0B Hospital A Richmond, VA 34 1B Hospital B Richmond, VA 27 1B Hospital C Midlothian, Va 5 0C Hospital A Richmond, VA 2 1C Hospital B Richmond, VA 9 1C Hospital C Midlothian, Va 25 1Desired Results:Procedure City, State Total Vol VF =1 Total Vol VF = 0Procedure A Richmond, VA 7 5Procedure A Midlothian, VA 0 5Procedure B Richmond, VA 61 0Procedure B Midlothian, VA 0 5Procedure C Richmond, VA 9 2Procedure C Midlothian, VA 25 0 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-06 : 17:48:23
|
| [code]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 TABLEGROUP BY [Procedure], City, STATE[/code] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-07 : 00:32:15
|
sorry your sample data doesnt make much sense. Can you expplain these lines?Procedure A Richmond, VA 7 5Procedure 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Two Different Totals