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

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.

• Q2 and Q3 are working as expected – sadeee nadeee Dec 3 '19 at 12:52
• You want multiple rows? – Rick James Dec 3 '19 at 22:22
• Yes, I want multiple rows – sadeee nadeee Dec 4 '19 at 10:00

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.

``````SELECT '2019-12-02' AS `Date`,
SUM(cg_response_code = 0)
+ SUM(     topup_id IN (10 , 11)
AND topup_cg_response_code LIKE '0'
) AS `Sum`
FROM XAXBXC_charge_info_ABC_tbl
WHERE `date` = '2019-12-02'
``````

PS. `LIKE '0'` condition is too strange. I'd recommend to replace it with `= 0` or `= '0'`, depending on the `topup_cg_response_code` datatype.

This will work for a particular day, but I want records for each day – sadeee nadeee 2 mins ago

``````SELECT `date`,
SUM(cg_response_code = 0)
+ SUM(     topup_id IN (10 , 11)
AND topup_cg_response_code LIKE '0'
) AS `Sum`
FROM XAXBXC_charge_info_ABC_tbl
/* WHERE `date` BETWEEN @date1 AND @date2 */
GROUP BY `date`
``````
• This will work for a particular day, but I want records for each day – sadeee nadeee Dec 4 '19 at 10:01