Sum counts according to status

Query 1: 000015, 2, 9
Query 2: 000015, 1, 10

I want something like this: 000015, 3, 19

Sum the results according to productCode.

You can do

Select

productcode,

sum(column1),Sum(column 2)

Group by productcode

But I have two tables so I have so queries?? UNION might solve?

SELECT productcode, Sum(CASE WHEN status IN ( 0, 2 ) THEN 1 ELSE 0 END) AS 'CANCEL\UNCERTAIN', Sum(CASE WHEN status IN ( 1 ) THEN 1 ELSE 0 END) AS CONFIRM FROM GameConfirmResponses WHERE requestdatetime >= '20191118' AND requestdatetime < '20191121' GROUP BY productcode
UNION
SELECT productcode, Sum(CASE WHEN status IN ( 0, 2 ) THEN 1 ELSE 0 END) AS 'CANCEL\UNCERTAIN', Sum(CASE WHEN status IN ( 1 ) THEN 1 ELSE 0 END) AS CONFIRM FROM GameBanks WHERE requestdatetime >= '20191118' AND requestdatetime < '20191121' GROUP BY productcode

hi

please try this.. should work for you !!!

please click arrow to the left for SQL
SELECT productcode, 
       Sum([cancel\uncertain]), 
       Sum(confirm) 
FROM   (SELECT productcode, 
               Sum(CASE 
                     WHEN status IN ( 0, 2 ) THEN 1 
                     ELSE 0 
                   END) AS 'CANCEL\UNCERTAIN', 
               Sum(CASE 
                     WHEN status IN ( 1 ) THEN 1 
                     ELSE 0 
                   END) AS CONFIRM 
        FROM   gameconfirmresponses 
        WHERE  requestdatetime >= '20191118' 
               AND requestdatetime < '20191121' 
        GROUP  BY productcode 
        UNION ALL
        SELECT productcode, 
               Sum(CASE 
                     WHEN status IN ( 0, 2 ) THEN 1 
                     ELSE 0 
                   END) AS 'CANCEL\UNCERTAIN', 
               Sum(CASE 
                     WHEN status IN ( 1 ) THEN 1 
                     ELSE 0 
                   END) AS CONFIRM 
        FROM   gamebanks 
        WHERE  requestdatetime >= '20191118' 
               AND requestdatetime < '20191121' 
        GROUP  BY productcode) a 
GROUP  BY productcode

What is the difference between my sample and yours? Does yours faster @harishgg1 ?

Mine takes
Both select statements

And combines into

One sum

Yours union

Will not take both selects

And combine into one sum

Yours union Will be 2 records

111 3. 4

    1. 10

Mine will be one record sum of both select

111 8. 14

If I want to add order by productCode at the end of your query, would it be OK?

Yes ..

That would be ok