Query counting NULL values - 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

Query counting NULL values

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2008-11-24 : 11:25:39

Hi,

I need to count all the orders that have Transaction='R' and Response='A' as "Approved Orders" and
also count orders where Transaction='C' and Response='A' as "Cancelled Orders'. I have the below query which is counting orders
which have Transaction='R' and Response=NULL and Transaction='C' and Response=NULL ( which I do not want to count).

select count(o.order_id) as Orders,
case
when TRANSACTION ='r'and RESPONSE='a' then 'Approved'
when TRANSACTION ='c'and RESPONSE ='a' then 'Declined'end
as Type
from orders_base o
inner join V_BillingWithHistory b
on o.order_id = b.order_id
where
o.partner like'%mem%'
and o.order_received_date >='5/1/2008' and o.order_received_date <'11/24/2008'
group by case
when TRANSACTION ='r'and RESPONSE ='a' then 'Approved'
when TRANSACTION ='c'and RESPONSE ='a' then 'Declined'end

This is the result set

Orders Type
1 NULL
167 Approved Billing
3 Approved Refund

Thanks,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 11:28:53

[code]select count(o.order_id) as Orders,
sum(case
when TRANSACTION ='r'and RESPONSE='a' then 1 else 0 end) AS 'Approved'
sum(case when TRANSACTION ='c'and RESPONSE ='a' then 1 else 0 end) AS 'Declined'
from orders_base o
inner join V_BillingWithHistory b
on o.order_id = b.order_id
where
o.partner like'%mem%'
and o.order_received_date >='5/1/2008' and o.order_received_date <'11/24/2008'
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 11:33:52

[code]SELECT COUNT(*) AS Orders,
theType
FROM (
SELECT CASE
WHEN Transaction ='r' AND Response = 'a' THEN 'Approved'
WHEN Transaction ='c' AND Response = 'a' THEN 'Declined'
END AS theType
FROM orders_base AS o
INNER JOIN V_BillingWithHistory AS b ON o.order_id = b.order_id
WHERE o.Partner LIKE '%mem%'
AND o.order_received_date >= '5/1/2008'
AND o.order_received_date <'11/24/2008'
) AS d
WHERE theType > ''
GROUP BY theType[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 11:37:38

[code]SELECT CASE Transaction
WHEN 'r' THEN 'Approved'
ELSE 'Declined'
END,
items
FROM (
SELECT Transaction,
COUNT(*) AS Items
FROM orders_base AS o
INNER JOIN V_BillingWithHistory AS b ON o.order_id = b.order_id
WHERE Response = 'a'
AND o.Partner LIKE '%mem%'
AND o.order_received_date >= '5/1/2008'
AND o.order_received_date <'11/24/2008'
GROUP BY Transaction
) AS d[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 11:43:05

[code]select count(Orders), Type
from
(
select o.order_id) as Orders,
case
when TRANSACTION ='r'and RESPONSE='a' then 'Approved'
when TRANSACTION ='c'and RESPONSE ='a' then 'Declined'end
as Type
from orders_base o
inner join V_BillingWithHistory b
on o.order_id = b.order_id
where
o.partner like'%mem%'
and o.order_received_date >='5/1/2008' and o.order_received_date <'11/24/2008'
)t
WHERE Type IS NOT NULL
group by Type[/code]

Petronas
Posting Yak Master

134 Posts

Posted - 2008-11-24 : 11:53:49

Thank you so much Visakh16 & Peso for your time. It worked for me.

Thanks again,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 11:55:26

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources