CODEWITHSUNDEEP
sqlsumcase
user1902717
user1902717

Reputation: 31

Use of sum and count inside a case statement in sql

select req.code ,res.code,
case
( 
when (req.code==res.code) then 'pass'
when (req.code<>res.code) then 'fail'
/*....2 more case 'when' 'then' statements here*/
end ) result,

req.country ,res.country,

case (when then staments as above)result,
/*.......case stmts upto 70 statemnts*/
from requesttable req full outer join responsetable res on
req.id=res.id 
and ....some conditions.

Can anyone tell me how can I sum every column and display the sum as well as the count of records in every column of both tables simultaneously and display count in my query?

My result should be of this sort

code  code1  result  sum     sum1    equivalence country country1 result1  sum    sum1
100   100    pass    200000  25000   fail        ind     aus      fail     800000 800000

equivalence
pass

I am trying to prepare a report joining two tables. I am using multiple case statements to accomplish this. I want to display sum of each column and count of each column of both the tables together in a single report. The query that I have is of the following type.

Upvotes: 3

Views: 12772

Answers (1)

Eric J. Price
Eric J. Price

Reputation: 2785

I think this is kind of what you're looking for. For the code and country values displayed on the line it would give you the pass and fail accounts for the combinations displayed and you would have uniqueness on the columns the aggregate is defined on. 

Select  req.code, 
        res.code,
        Sum(Case When req.code = res.code) Then 1 Else 0 End) As [Pass],
        Sum(Case When req.code <> res.code) Then 1 Else 0 End) As [Fail],
        req.country, 
        res.country,
        Sum(Case When req.country = res.country) Then 1 Else 0 End) As [Pass],
        Sum(Case When req.country <> res.country) Then 1 Else 0 End) As [Fail]
From    requesttable req
Full    Outer Join responsetable res
        On  req.id = res.id
Where   ...
Group   By  req.code,
            res.code,
            req.country,
            res.country

Upvotes: 1

Related Questions