Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: problem on a view which is not giving all the records

    HI I have this query and it works fine until i insert a where clause before the group by.

    SELECT GR, SUM(CASE WHEN START>= '20100101' AND START< '20100101' THEN 1 ELSE 0 END) AS total
    FROM Company
    GROUP BY GR
    ORDER BY
    CASE GR
    WHEN 'A' then 1
    WHEN 'B' then 2
    WHEN 'C' then 3
    WHEN 'D' then 4
    WHEN 'E' then 5
    WHEN 'F' then 6
    WHEN 'G' then 7
    WHEN 'H' then 8
    WHEN 'I' then 9
    else 99 END

    RESULT
    A 0
    B 5
    C 0
    D 0
    E 0
    F 2
    G 2
    H 0
    I 0

    UP TO HERE IS FINE BUT WHEN i INSERT A WHERE CLASE BEFORE THE GROUP BY

    SELECT GR, SUM(CASE WHEN START>= '20100101' AND START< '20100101' THEN 1 ELSE 0 END) AS total
    FROM Company
    WHERE PR='ABC'
    GROUP BY GR
    ORDER BY
    CASE GR
    WHEN 'A' then 1
    WHEN 'B' then 2
    WHEN 'C' then 3
    WHEN 'D' then 4
    WHEN 'E' then 5
    WHEN 'F' then 6
    WHEN 'G' then 7
    WHEN 'H' then 8
    WHEN 'I' then 9
    else 99 END

    IT ONLY GIVES ME THIS RESULT

    B 5
    C 0
    D 0
    E 0
    F 2

    COULDN'T FIND WHAT'S WRONG..

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rows where GR = 'A' do not have any entries of 'ABC' in PR. Simples.

  3. #3
    Join Date
    Aug 2006
    Posts
    87
    Quote Originally Posted by pootle flump View Post
    rows where gr = 'a' do not have any entries of 'abc' in pr. Simples.
    how could i get it to display 0 for a if it doesn't find a record?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Move the ABC bit out of the WHERE clause and in to the CASE statement. Exactly the same logic as you used for the date check. In other words - you already knew the answer!

  5. #5
    Join Date
    Aug 2006
    Posts
    87
    Thanks a lot you're right i haven't figured that out earlier

  6. #6
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    Another option is to use the All keyword in the Group By clause.

    SELECT GR, SUM(CASE WHEN START>= '20100101' AND START< '20100101' THEN 1 ELSE 0 END) AS total
    FROM Company
    WHERE PR='ABC'
    GROUP BY ALL GR

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Now that is a gorgeous solution, except that GROUP BY ALL is deprecated and will be removed in a future version.

Posting Permissions

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