Author Topic: Case statement with multiple condition in one query item  (Read 275 times)

Offline inu

  • Statesman
  • ******
  • Join Date: Nov 2013
  • Posts: 422
  • Forum Citizenship: +0/-28
Hi,
I created a query item as below
Count (distinct case when item =1 then Id else null end)
Like this I have more 4 condition with same Id count
Count (distinct case when item =2 then Id else null end)
Count (distinct case when item =3 then Id else null end)
Count (distinct case when item =4 then Id else null end)
Count (distinct case when item =5 then Id else null end)
In spite of creating separate query item,  can we create single query item.

Thanks
Inam\

Offline RichardP

  • Full Member
  • ***
  • Join Date: May 2017
  • Posts: 15
  • Forum Citizenship: +3/-0
Re: Case statement with multiple condition in one query item
« Reply #1 on: 08 Nov 2017 09:32:24 pm »
Hi,
I created a query item as below
Count (distinct case when item =1 then Id else null end)
Like this I have more 4 condition with same Id count
Count (distinct case when item =2 then Id else null end)
Count (distinct case when item =3 then Id else null end)
Count (distinct case when item =4 then Id else null end)
Count (distinct case when item =5 then Id else null end)


Hmmm.
I think they way you have defined it is correct.
It would appear to want separate DISTINCTly counted measures
Having these in a single CASE statement for example I don't think would work

ex.
CASE
   WHEN [Gender] = 'M' then [Employee_ID]  ELSE null
   WHEN [Gender] = 'F' then [Employee_ID]  ELSE null
   ELSE null
END

This above would not give you separate counts for Male and Female.
Like your example - you would need to have separate expressions

Name:  Male Count
IF [Gender] = 'M' then [Employee_ID]  ELSE null

Name:  Female Count
IF [Gender] = 'F' then [Employee_ID]  ELSE null
   
Good luck

 


       
Twittear