2 search criteria in one query

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

SQL Server 2000 Forums

Transact-SQL (2000)

2 search criteria in one query

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-01 : 11:29:52

I have a table with 3 columns
Disp varchar(10),
itmVal Money,
CRX varchar(1)

I need to have a query to get me the
sum of itmVal when CRX is null in one column (in the results) &
sum of itmVal when CRX is not null

The hypothetical Query looks like the following

Select Disp, RxAmt, NCDAmt from
(Select
Disp,
RxAmt = case when CRX is null then sum(itmVal),
NCDAmt = case when CRX is not null then sum(itmVal)
from #Temp
Group by Disp, CRX)

Following can be used for testing:

Create Table #Temp (Disp varchar(10), itmVal Money, CRX varchar(1))

Insert Into #Temp values('aaa',10,Null)
Insert Into #Temp values('aaa',15,Null)
Insert Into #Temp values('aaa',20,'C')
Insert Into #Temp values('bbb',25,Null)
Insert Into #Temp values('bbb',30,'C')
Insert Into #Temp values('ccc',10,Null)
Insert Into #Temp values('ddd',15,Null)
Insert Into #Temp values('ddd',20,Null)
Insert Into #Temp values('ddd',25,'C')
Insert Into #Temp values('ddd',30,'C')
Insert Into #Temp values('eee',15,'C')
Insert Into #Temp values('eee',10,'C')

The result I expect from the select qry is:


Disp	RxAmt	NCDAmt	
----	-----	------
aaa	25	20
bbb	25	30
ccc	10	 0
ddd	35	55		
eee	 0	25

-- Drop table #Temp

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-01 : 11:45:38

[code]
select distinct Disp, sum(RxAmt), sum(NCDAmt) from
(
select Disp,
sum(case when CRX is null then itmVal else 0 end) as RxAmt,
sum(case when CRX is not null then itmVal else 0 end) as NCDAmt
from #Temp
Group by Disp, CRX) a
group by Disp
[/code]

szgldt
Starting Member

10 Posts

Posted - 2006-02-01 : 11:50:03

A few ways to do this but something like this work:

SELECT Disp, RXAmtTable.RxAmt, NCDAmtTable.NCDAmt
FROM #Temp
INNER JOIN (SELECT Disp, Sum(itmVal) as RxAmt
FROM #Temp
WHERE CRX is Null
GROUP BY Disp) as RXAmtTable
ON #Temp.Disp = RXAmtTable.Disp
INNER JOIN (SELECT Disp, Sum(itmVal) as NCDAmt
FROM #Temp
WHERE CRX is not Null
GROUP BY Disp) as NCDAmtTable
ON #Temp.Disp = NCDAmtTable.Disp

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-01 : 11:59:01

Thanks RickD

It worked great

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-01 : 12:53:39

OK guys I have one more question
This is a continuation of the above query.

How can I get the count of records (different counts for CRX is null & not null) as well

ie, for my sample data the expected results :

Disp	RxAmt	NCDAmt    RxUnits   NCDUnits	
----	-----	------    -------   --------
aaa	25	20          2         1
bbb	25	30          1         1
ccc	10	 0          1         0
ddd	35	55	    2         2	
eee	 0	25          0         2

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-01 : 14:55:40

OK I got the expected results using szgldt's suggestion.

But I'd prefer to extend RickD's method because, I have a huge query instead of a #Temp table.
[I didn't give that since it makes things more complicated]

So, where ever the #Temp is used in szgldt's way I had to place my huge query.

Its working, no efficiency problem, but I prefer RickD's way as its easy to maintain.

If either of u or somebody has any idea on how to extend RickD's suggestion to achieve my 2nd request, please help me.

VIG
Yak Posting Veteran

86 Posts

Posted - 2006-02-01 : 15:28:23

select disp,
sum(case when CRX is null then itmVal else 0 end) RxAmt,
sum(case when CRX is not null then itmVal else 0 end) NCDAmt,
count(*)- count(crx) RxUnits ,
count(crx) NCDUnits
from #Temp
group by disp

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-01 : 16:30:56

Thanks a Lot VIG

It works like a charm

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-02 : 09:25:13

count(col) will omit the nulls so as Sum



select count(*) as Count_All,count(data1) as Count_Data1,sum(data1) as sum_data1 from
(
	select 12 as data1, 'test2' as data2 union all
	select 568 as data1, 'test' as data2 union all
	select Null as data1, 'test' as data2 union all
	select 6 as data1, 'test' as data2 
) T

Madhivanan

Failing to plan is Planning to fail

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-02 : 09:57:01

Yes Madhi,
I learned that when analysing VIGs query.
Urs is also a simple example to understand the same concept.

A big Thank for all of u.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources