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 |
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-03-21 : 04:43:54
|
column1 column2 column3 column4car blue driver1 passcar blue driver2 passcar red driver3 failedhow can i separate out the column4 into as below. is it possible?column1 column2 column3 column4 PassTotal FailedTotalcar blue driver1 pass 1 0car blue driver2 pass 1 0car red driver3 failed 0 1 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-21 : 04:54:42
|
[code]SELECT *,CASE WHEN column4 = 'pass' THEN 1 ELSE 0 END AS PassTotal,CASE WHEN column4='failed' THEN 1 ELSE 0 END AS FailedTotalFROM TAble[/code]Didnt understand need of this requirement though. Here you're actually creating redundant columns out of same column value (column4)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2013-03-21 : 05:02:28
|
is actually to count the total.Not to set 1 or 0. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-21 : 05:18:26
|
then why not do it like this?SELECT COlumn1,Colummn2,SUM(CASE WHEN column4 = 'pass' THEN 1 ELSE 0 END) AS PassTotal,SUM(CASE WHEN column4='failed' THEN 1 ELSE 0 END) AS FailedTotalFROM TAbleGROUP BY COlumn1,Column2 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ashabc
Starting Member
2 Posts |
Posted - 2013-03-22 : 19:42:58
|
My question is similar. So, I did not open a new topic. I have the following two SQL statements. Individually they return one column and returns the correct value.How can I join them together with two columns as the output with respective column headers?SELECT SUM(LASTVAL) AS icon_hq_ext FROM table1 where (MACHINE = '192.168.50.111' OR MACHINE = '192.168.50.112') AND (INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_S_443' OR INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_T_443')SELECT SUM(LASTVAL) AS icon_dr_ext FROM table1 where (MACHINE = '192.168.64.111' OR MACHINE = '192.168.64.112') AND (INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_443') |
|
|
ashabc
Starting Member
2 Posts |
Posted - 2013-03-23 : 03:33:02
|
OK, I got some work around. I have added a dummy column in the beginning and then put these two queries separated by a comma and it works. However, if I put only these two statements separated by a comma, it does not work (throws some syntax error)SELECT 'LA' as Location, (SELECT SUM(LASTVAL) FROM [ArgentATLive].[dbo].[ARGSOFT_SN_PRD_MOTHER] where (MACHINE = '192.168.50.111' OR MACHINE = '192.168.50.112') AND (INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_S_443' OR INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_T_443')) AS icon_hq_ext, (SELECT SUM(LASTVAL) FROM [ArgentATLive].[dbo].[ARGSOFT_SN_PRD_MOTHER] where (MACHINE = '192.168.64.111' OR MACHINE = '192.168.64.112') AND (INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_443')) AS icon_dr_extWould be nice to have only two columns instead of three (first one being dummy) |
|
|
|
|
|
|
|