I'm using two cases separately to display the result as two columns, but I'm getting the result of only first case (for members with 11). I'm not getting any result for members with 99.
I have a table with a column member_type_id. This column has two sets of values 11 and 99. I want to group my entire table based on those two values. Any help is greatly appreciated!
Create table main
(name varchar,
member_type_id integer)
Following are the values in main table.
Name member_type_id
Sara 11
Jack 11
Jill 99
Mary 99
Anna 99
SELECT CASE
WHEN MEMBER_TYPE_ID = 11 THEN COUNT(MEMBER_TYPE_ID) OVER (PARTITION BY MEMBER_TYPE_ID)
END emp_count,
CASE
WHEN MEMBER_TYPE_ID = 99 THEN COUNT(MEMBER_TYPE_ID) OVER (PARTITION BY MEMBER_TYPE_ID)
END sp_count
FROM main
When I run the above query in sql server I get following result
emp_count
2
2
sp_count
3
3
3
However, when I run this query in crystal report, I get the result of only emp_count i.e 2. For sp_count nothing shows.