CODEWITHSUNDEEP
phpmysqlsqlselectsubquery
mrdragon
mrdragon

Reputation: 247

MySQL query and subquery

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?

Upvotes: 4

Views: 69

Answers (3)

Zafar Malik
Zafar Malik

Reputation: 6844

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)

Upvotes: 2

Mureinik
Mureinik

Reputation: 311073

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')

Upvotes: 3

Madhivanan
Madhivanan

Reputation: 13700

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')

Upvotes: 2

Related Questions