1

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
0

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.

0
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
  • @sadeeenadeee This is not a problem... added. – Akina Dec 4 '19 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.