SQL multiple select with multiple where condition

Question

I have simple table

ID   usr_id       card_amount     state     created
----------------------------------------------------------------
1    a1          10.000            2      2014-03-13 14:33:39
2    a2          30.000            2      2014-03-11 14:33:39
3    a3          50.000            1      2014-03-10 14:33:39
4    a4          20.000            2      2014-04-13 14:33:39
5    a5          40.000            2      2014-03-19 14:33:39
----------------------------------------------------------------

I have this query but it take too long time ! Help me solution to make this faster

SELECT 
    DATE_FORMAT(`created`, '%Y-%m-%d') AS `key_date`,       
    (
        SELECT 
            SUM(`card_amount`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` = 2 AND  `created` >= `key_date` AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `totalamount`,
    (
        SELECT 
            COUNT(`id`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` =2 AND `created` >= `key_date` AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `turncharge`,
    (
        SELECT 
            COUNT(DISTINCT `usr_id`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` =2 AND `created` >= `key_date` AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `pu`,
    (
        SELECT 
            SUM(`card_amount`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `totalamount7`,            
    (
        SELECT 
            COUNT(`id`) 
        FROM `payment` WHERE `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY)  
    ) AS `turncharge7`,             
    (
        SELECT
            COUNT(DISTINCT `usr_id`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `pu7`,
    (
        SELECT 
            SUM(`card_amount`) 
        FROM `payment` WHERE `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `totalamount30`,           
    (
        SELECT 
            COUNT(`id`) 
        FROM `payment` WHERE `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `turncharge30`,            
    (
        SELECT
            COUNT(DISTINCT `usr_id`) 
        FROM `payment` 
        WHERE `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) 
    ) AS `pu30`
FROM `payment`          
WHERE MONTH(`created`)=$month AND YEAR(`created`)=$year         
GROUP BY `key_date`         
ORDER BY `key_date` DESC

I use LEFT JOIN but it don't give what i need : c1.totalamount = c2 totalmount7, c1.turncharge =c2.turncharge7 ........(not right)

SELECT DATE_FORMAT(`created`, '%Y-%m-%d') AS `key_date`,c1.totalamount,c1.turncharge,c1.pu,c2.totalamount7,c2.turncharge7,c2.pu7
FROM `payment` 

LEFT JOIN (SELECT DATE_FORMAT(`created`, '%Y-%m-%d') AS `date1`,SUM(`card_amount`) AS `totalamount`, COUNT(`id`) AS `turncharge`,COUNT(DISTINCT `usr_id`) AS `pu` 
FROM `payment` 
WHERE `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_FORMAT(`created`, '%Y-%m-%d')
AND `created` < DATE_SUB(DATE_FORMAT(`created`, '%Y-%m-%d'),INTERVAL -1 DAY)  GROUP BY date1) AS c1 ON date1 = DATE_FORMAT(`created`, '%Y-%m-%d')

LEFT JOIN (SELECT DATE_FORMAT(`created`, '%Y-%m-%d') AS `date2`,SUM(`card_amount`) AS `totalamount7`, COUNT(`id`) AS `turnchargep7`,COUNT(DISTINCT `usr_id`) AS `pu7` 
FROM `payment` 
WHERE `card_amount`> 0 AND `state` = 2 AND  created >= DATE_SUB(DATE_FORMAT(`created`, '%Y-%m-%d'),INTERVAL 6 DAY)   
AND `created` < DATE_SUB(DATE_FORMAT(`created`, '%Y-%m-%d'),INTERVAL -1 DAY) GROUP BY date2) AS c2 ON date2 = DATE_FORMAT(`created`, '%Y-%m-%d')

        WHERE MONTH(`created`)=4 AND YEAR(`created`)=2014
        GROUP BY `key_date`
        ORDER BY `key_date` DESC

Thanks Mladen Prajdic I use CASE expression and it worked faster

SELECT date.key_date,
SUM(case when `card_amount`> 0 AND `state` = 2 AND  `created` >= `key_date` AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) then `card_amount` else 0 end) AS totalamount,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` =2 AND `created` >= date.`key_date` AND `created` < DATE_SUB(date.`key_date`,INTERVAL -1 DAY) then payment.`id` else 0 end)) AS `turncharge`,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` =2 AND `created` >= `key_date` AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY)  then `usr_id` else 0 end) )AS `pu` ,

SUM(case when `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) then `card_amount` else 0 end) AS `totalamount7`,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` =2 AND `created` >= DATE_SUB(date.`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(date.`key_date`,INTERVAL -1 DAY)  then payment.`id` else 0 end)) AS `turncharge7`,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(date.`key_date`,INTERVAL 6 DAY) AND `created` < DATE_SUB(date.`key_date`,INTERVAL -1 DAY)  then `usr_id` else 0 end) )AS `pu7` ,


SUM(case when `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY) then `card_amount` else 0 end) AS `totalamount30`,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(date.`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(date.`key_date`,INTERVAL -1 DAY)  then payment.`id` else 0 end)) AS `turncharge30`,
COUNT(DISTINCT(case  when `card_amount`> 0 AND `state` = 2 AND  `created` >= DATE_SUB(`key_date`,INTERVAL 29 DAY) AND `created` < DATE_SUB(`key_date`,INTERVAL -1 DAY)  then `usr_id` else 0 end) )AS `pu30` 

FROM `payment`,(SELECT DATE_FORMAT(`created`, '%Y-%m-%d') AS `key_date` FROM `payment` 
WHERE MONTH(`created`)=3 AND YEAR(`created`)=2014
GROUP BY `key_date`) AS date

GROUP BY date.key_date
ORDER BY date.key_date DESC

when you do a sub select like that it will take forever because for each sub select it has to compare against every row in your table. so for instance if you only had 1000 rows in payments then just to do the selects it has to compare each one with the 1000 rows.. doing 9000 comparisons... not even accounting for all of the other stuff you do in it. I would recommend looking into multiple queries for this, or doing joins. — John Ruddell
– John Ruddell, Commented Apr 5, 2014 at 4:55

Mladen Prajdic · Accepted Answer · 2014-04-06 17:03:05Z

1

Rewrite your correlated subqueries with case statements. This will make a single pass through your table. How is shown in this article:
Rewriting correlated sub-queries with CASE expressions

answered Apr 6, 2014 at 17:03

Mladen Prajdic

15.7k2 gold badges46 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

SQL multiple select with multiple where condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related