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

1. Registered User
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. King of Understatement
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. Registered User
Join Date
Aug 2006
Posts
87
Originally Posted by pootle flump
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. King of Understatement
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. Registered User
Join Date
Aug 2006
Posts
87
Thanks a lot you're right i haven't figured that out earlier

6. Registered User
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. King of Understatement
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
•