CASE - How to show groups with count(*) = 0 (zero) ?

I would like to show even the rows where number of records are zero (in the Case statement ).

I couldn't find the solution either in the microsoft newsgroup, dbforums or Google. I fear I am overlooking something very very basic!
Thanks in advance.
:)

SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end,
Count(*) 'Number of Invoices'

FROM AP

GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end
ORDER BY Min(Amount)
----------
Suppose these are the Amounts (99,50,75,201,230)
The CURRENT OUTPUT is
<= 100 3
>200 2

The REQUIRED OUTPUT is
<= 100 3
<= 200 0
> 200 2select 'Unknown', count(*)
from AP
where Amount IS NULL
UNION ALL
select '<= 100 ', count(*)
from AP
where Amount <= 100
UNION ALL
select '<=200', count(*)
from AP
where Amount <= 100 AND Amount > 100
UNION ALL
select '>200', count(*)
from AP
where Amount > 200|||correction:
where Amount <= 200 AND Amount > 100|||Thanks HanafiH.

Using union will cause a pass through the table each time. That will generate a performance overhead which would increase as the number of divisions increase. I am generating this CASE statement using dynamic SQL. The application can provide any value to the number of divisions.

I want to take advantage of the performance benefit by using CASE.

Is there a solution using CASE itself?

Thanks again in advance.
:)|||declare @.GroupingTable table
(GroupingValue varchar(10),
SortOrder int)

insert into @.GroupingTable (GroupingValue, SortOrder) values('Unknown', 1)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 100 ', 2)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 118 ', 3)
insert into @.GroupingTable (GroupingValue, SortOrder) values('> 200', 4)

select GroupingTable.GroupingValue
from
@.GroupingTable GroupingTable
left outer join
(SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end GroupingValue
Count(*) 'Number of Invoices'
FROM AP
GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end) SummaryData
on GroupingTable.GroupingValue = SummaryData.GroupingValue
order by GroupingTable.SortOrder

blindman|||Originally posted by HornOkPlease
Thanks HanafiH.

Using union will cause a pass through the table each time. That will generate a performance overhead which would increase as the number of divisions increase. I am generating this CASE statement using dynamic SQL. The application can provide any value to the number of divisions.

I want to take advantage of the performance benefit by using CASE.

Is there a solution using CASE itself?

Thanks again in advance.
:)

The CASE may well be efficient, but the GROUP BY and ORDER BY necessary to make use of it is not. UNION *ALL* is very efficient. On my system your original case query has a 0.0604 total query cost, my query runs at 0.0192 total query cost, which is roughly three times faster than the CASE case (as it were).

I shudder to think about what insertion into a temp will cost.|||try this:select count(*) as TotalNumber
, sum(case when amount is null
then 1 else 0 end) as Unknown
, sum(case when amount <= 100
then 1 else 0 end) as "<= 100"
, sum(case when amount <= 200
then 1 else 0 end) as "<= 200"
, sum(case when amount > 200
then 1 else 0 end) as "> 200"
from ap
you guys with the cpu timers, please let me know how this compares to the other solutions

rudy
http://r937.com/|||r937, I think you hit the nail on the head.

blindman|||Hats off to you blindman!
Thanks, HanafiH, i've taken note of your message too.

Regards,
HornOkPlease|||Originally posted by r937
try this:select count(*) as TotalNumber
, sum(case when amount is null
then 1 else 0 end) as Unknown
, sum(case when amount <= 100
then 1 else 0 end) as "<= 100"
, sum(case when amount <= 200
then 1 else 0 end) as "<= 200"
, sum(case when amount > 200
then 1 else 0 end) as "> 200"
from ap
you guys with the cpu timers, please let me know how this compares to the other solutions

rudy
http://r937.com/

0.0376|||Originally posted by blindman
declare @.GroupingTable table
(GroupingValue varchar(10),
SortOrder int)

insert into @.GroupingTable (GroupingValue, SortOrder) values('Unknown', 1)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 100 ', 2)
insert into @.GroupingTable (GroupingValue, SortOrder) values('<= 118 ', 3)
insert into @.GroupingTable (GroupingValue, SortOrder) values('> 200', 4)

select GroupingTable.GroupingValue
from
@.GroupingTable GroupingTable
left outer join
(SELECT case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end GroupingValue
Count(*) 'Number of Invoices'
FROM AP
GROUP BY case
WHEN Amount is null then 'Unknown'
WHEN Amount <= 100 THEN '<= 100 '
WHEN Amount <= 200 THEN '<= 118 '
ELSE '> 200'
end) SummaryData
on GroupingTable.GroupingValue = SummaryData.GroupingValue
order by GroupingTable.SortOrder

blindman

0.1158 and the slowest solution proposed.|||I think r937 was being modest. You don't need to time these solutions to see that his is better. It's the solution I was trying to get to, but I guess I was one or two cups of coffee short this morning.

blindman