How to return count in 2 columns?

Question

I have this query. It should return Count for both AWARDED (1) and NOT AWARDED(0) works from works table.

        Select Count(w.WorkID)as Total, w.IsAwarded, org.OrganizationName
        From Works w
        Inner Join MC_MemberShip.Membership.Organization org
        ON org.OrganizationID= w.Organization_ID
        where Convert(varchar(11), w.OpeningDate) >= Convert(varchar(11), @FromDate) 
        and  Convert(varchar(11), w.OpeningDate) < DATEADD(day, 1, Convert(varchar(11), @ToDate))
        and w.IsActive=1 and 
        ISNULL(w.IsAwarded,0)= 0 and w.Organization_ID= case when @OrgID= -1 then w.Organization_ID else @OrgID end
        group by org.OrganizationName, w.IsAwarded

Now this query returns Total count for NOT AWARDED i.e. 0 only but i want to return count for AWARDED too in same query.

Organization    TotalAwardedWorks    TotalNotAwardedWorks
Town 1              1                        2
Town 2              44                       33

Add some sample table data and the expected result - as well formatted text. — jarlh
– jarlh, Commented Apr 13, 2017 at 11:45

Gordon Linoff · Accepted Answer · 2017-04-13 12:14:06Z

2

Your query should look something like this:

select org.OrganizationName,
       Count(*) as Total,
       sum(case when w.IsAwarded = 0 or w.IsAwarded is null then 1 else 0 end) as TotalNotAward,
       sum(case when w.IsAwarded = 1 then 0 else 1 end) as TotalAward
from Works w Inner Join
     MC_MemberShip.Membership.Organization org
     on org.OrganizationID = w.Organization_ID
where w.OpeningDate >= @FromDate and
      w.OpeningDate < dateadd(day, 1, @ToDate) and
      w.IsActive = 1 and 
      (w.Organization_ID = @OrgId or @OrgID= -1)
group by org.OrganizationName;

Notes:

Do not convert dates to strings to perform comparisons. That is just perverse.
Generally, the use of case in the where clause is discouraged. The logic is more clearly represented using or.
You can get what you want by using case to put conditions in the aggregation functions.

edited Apr 13, 2017 at 12:14

answered Apr 13, 2017 at 11:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Covert Over a year ago

sum(case when w.IsAwarded = 1 then 1 else 0 end) as TotalAwarded, sum(case when w.IsAwarded = 0 or w.IsAwarded is null then 0 else 1 end) as TotalNotAwarded

Collectives™ on Stack Overflow

How to return count in 2 columns?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related