Mysql – How to get sum of 3 sub count queries and group them using an attribute

countgroup byMySQLselectsubquery

I have 3 queries as follows:

For the date 2019-12-02, I am calculating 3 type of values. The total value for 2019-12-02 must be the sum of these 3 queries.

Q1 

SELECT count(*) 
FROM XAXBXC_charge_info_ABC_tbl 
WHERE date = '2019-12-02' 
AND cg_response_code = 0

Q2=> 

SELECT count(*) 
FROM XAXBXC_charge_info_ABC_tbl 
WHERE date = '2019-12-02' 
  AND topup_id = 10 
  AND topup_cg_response_code LIKE '0'

Q3=-> 

SELECT count(*) 
FROM XAXBXC_charge_info_ABC_tbl 
WHERE date = '2019-12-02' 
  AND topup_id = 11 
  AND topup_cg_response_code LIKE '0'

I am trying to calculate and display sum as follows,

Date      | Sum 
2019-12-02|  234

eg:-

For a particular date, sum should be equal to the sum of Q1+Q2+Q3 and it must be grouped by the date column.

Can someone help me to achieve this? I have been stuck here for while now.

Best Answer

To get one row per day,

SELECT date,
       SUM( cg_response_code = 0 ) +
         SUM( topup_id IN (10, 11) AND topup_cg_response_code = 0 )  AS "Sum"
    FROM XAXBXC_charge_info_ABC_tbl 
    GROUP BY date

The pattern SUM(boolean) is a way to count the number of rows where "boolean" is TRUE.

This does all the work in a single pass over the data.

Related Question