Query 1: 000015, 2, 9
Query 2: 000015, 1, 10
I want something like this: 000015, 3, 19
Sum the results according to productCode.
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 !!!
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
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
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