Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    5

    Unanswered: 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 2

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    select '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

  3. #3
    Join Date
    Nov 2003
    Posts
    94
    correction:
    where Amount <= 200 AND Amount > 100

  4. #4
    Join Date
    Dec 2003
    Posts
    5

    Any way using Case ?

    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  6. #6
    Join Date
    Nov 2003
    Posts
    94

    Re: Any way using Case ?

    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.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this:
    Code:
    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/

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    r937, I think you hit the nail on the head.

    blindman

  9. #9
    Join Date
    Dec 2003
    Posts
    5

    Wonderful solution

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

    Regards,
    HornOkPlease

  10. #10
    Join Date
    Nov 2003
    Posts
    94
    Originally posted by r937
    try this:
    Code:
    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

  11. #11
    Join Date
    Nov 2003
    Posts
    94
    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.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •