-1

I have a variable named status in which there are values like:

  • Expired
  • No Interest
  • Already Engaged
  • Never Reached
  • Non-Supported
  • Market Nurture

I want to get count of all these.

Do I have to write manually like:

select 
sum (case when a.[Status Reason] ='Expired' and a.status='Disqualififed' then 1 else 0 end ) as expired,
sum (case when a.[Status Reason] ='No interest' and a.status='Disqualififed' then 1 else 0 end ) as nointerst from lead

or is there any shortcut or some kind of automation possible or dynamic way so that i can avoid writing sql for each value?

Keith
  • 1,008
  • 10
  • 19
Adi
  • 329
  • 1
  • 8
  • You literally just posted this: [get count of all values of a variable in sq](https://stackoverflow.com/q/60667848/2029983). If you don't feel the question is a flagged duplicate, then explain *why*, and show your attempt. if you don't understand it, then tell us what you don't understand, and show us your attempt(s). Reposting your question (exactly) doesn't change the fact that it''s seen as a duplicate. – Thom A Mar 13 '20 at 09:46
  • no that is dynamic pivot – Adi Mar 13 '20 at 09:47
  • i want to get count of all values of variable – Adi Mar 13 '20 at 09:48
  • There are no variables in your SQL, @Adi , and what you *describe* **is** a Dynamic Pivot. What you have in your example SQL is a Cross-Tab, which is a form of pivoting. – Thom A Mar 13 '20 at 09:48
  • but for pivoting ,you should have all the values prior to it. Here objective is to get count – Adi Mar 13 '20 at 09:49
  • my task is to get count not pivot or transpose the data . – Adi Mar 13 '20 at 09:51
  • Get count of "what"? At least, then, show us some sample data and expected results. – Thom A Mar 13 '20 at 09:52
  • Suppose there is a column "status" having values like "Expired", "No Interest", "Already Engaged", "Never Reached ","Non-Supported"," Market Nurture" .So get the count of all values of column status like count of expired, count of no interest and so on.. – Adi Mar 13 '20 at 10:12
  • What does that have to do with a Variable, @Adi ? And that is, again, you describing a Dynamic Pivot. Which your I have already flagged your prior question as a duplicate of. – Thom A Mar 13 '20 at 10:14
  • Suppose there is a column "status" having values like "Expired", "No Interest", "Already Engaged", "Never Reached ","Non-Supported"," Market Nurture" .So get the count of all values of column status like count of expired, count of no interest and so on. – Adi Mar 13 '20 at 10:15
  • Again, that is a Dynamic Pivot... Take the time to read the [duplicate](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query). If you don't think it'#s a duplicate ***explain*** why. If you fail to implement the solution, ask a question ***with*** your attempts and *explain* why they didn't work. If you don't understand the syntax, you could leave a comment on the answers or ask about that. Don't just repost your question again. You have the answer in front of you, you just need to implement it. – Thom A Mar 13 '20 at 10:16

1 Answers1

0

You can eliminate second condition : 

select sum(case when l.[Status Reason] = 'Expired' then 1 else 0 end ) as expired,
       sum(case when l.[Status Reason] = 'No interest' then 1 else 0 end ) as nointerst 
from lead l
where l.status = 'Disqualififed';
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52