MySQL query and subquery

Question

I have a query like this:

SELECT SUM(price) AS GINTO,
(
   (SELECT COUNT(price)*9.99 FROM sms_logs WHERE price = '200000')+
   (SELECT COUNT(price)*3.99 FROM sms_logs WHERE price = '60000')+
   (SELECT COUNT(price)*1.99 FROM sms_logs WHERE price = '24000')+
   (SELECT COUNT(price)*0.99 FROM sms_logs WHERE price = '11000')
) AS USD,
DATE_FORMAT(DATE,'%Y-%m-%d') AS DATE FROM sms_logs 
WHERE DATE >='2015-03-20' AND DATE <= '2015-04-30'
GROUP BY DATE_FORMAT(DATE,'%Y-%m-%d')

I want show data as:

 GINTO  ||   USD  ||   DATE
2222000 ||   200  || 2015-03-23
3366000 ||   300  || 2015-03-24
11000   ||   10   || 2015-03-25

But result with that query:

 GINTO  ||   USD ||   DATE
2222000 || 284.65|| 2015-03-23
3366000 || 284.65|| 2015-03-24
11000   || 284.65|| 2015-03-25

with USD column it shows a summary of all records. Why?

You need to provide some sample data and the logic of the expected result set,best is sample data in sqlfiddle.com — Abhik Chakraborty
– Abhik Chakraborty, Commented Jun 25, 2015 at 9:29

Mureinik · Accepted Answer · 2015-06-25 09:33:10Z

3

Your count calls are run in separate queries, with no group by clause, so they are applied on the entire table, regardless of the "main" query's goruping. One way to solve this would be to get rid of the subqueries and bring them into the "main" query:

SELECT   SUM(price) AS GINTO,
         SUM(CASE price WHEN '200000' THEN 9.99
                        WHEN '60000'  THEN 3.99
                        WHEN '24000'  THEN 1.99
                        WHEN '11000'  THEN 0.99
              END) AS usd,
         DATE_FORMAT(date, '%Y-%m-%d') AS date 
FROM     sms_logs 
WHERE    date >='2015-03-20' AND date <= '2015-04-30'
GROUP BY DATE_FORMAT(date,'%Y-%m-%d')

answered Jun 25, 2015 at 9:33

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Madhivanan · Accepted Answer · 2015-06-25 09:30:31Z

2

Try this

SELECT SUM(price) AS GINTO,
SUM(case when price = '200000' then 1 else 0 end*9.99)+
SUM(case when price = '60000' then 1 else 0 end*3.99)+
SUM(case when price = '24000' then 1 else 0 end*1.99)+
SUM(case when price = '11000' then 1 else 0 end*0.99) AS USD,
DATE_FORMAT(DATE,'%Y-%m-%d') AS DATE FROM sms_logs 
WHERE DATE >='2015-03-20' AND DATE <= '2015-04-30'
GROUP BY DATE_FORMAT(DATE,'%Y-%m-%d')

answered Jun 25, 2015 at 9:30

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Comments

RiggsFolly · Accepted Answer · 2015-06-25 09:34:36Z

2

you can try below query-

SELECT SUM(price) AS GINTO, 
      (COUNT(IF(price='200000',price,NULL))*9.99 +  
       COUNT(IF(price='60000',price,NULL))*3.99 + 
       COUNT(IF(price='24000',price,NULL))*1.99 + 
       COUNT(IF(price='11000',price,NULL))*0.99) AS USD, 
DATE_FORMAT(date_col,'%Y-%m-%d') AS 'Date' 
FROM sms_logs 
WHERE date_col >='2015-03-20' AND date_col <= '2015-04-30'
GROUP BY DATE(date_col)

edited Jun 25, 2015 at 9:34

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

answered Jun 25, 2015 at 9:33

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

Collectives™ on Stack Overflow

MySQL query and subquery

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related