Reputation: 4406

MySQL: Select from "data list"

For some calculations I need the sum of some table entries for the last 30 days. My idea is to do something like:

SELECT
   a.`date`,
   (SELECT COUNT(*) FROM `subtable1` AS b WHERE b.`date` = a.`date`) AS `sum1`,
   (SELECT COUNT(*) FROM `subtable2` AS c WHERE c.`date` = a.`date`) AS `sum2`,
   (SELECT COUNT(*) FROM `subtable3` AS d WHERE d.`date` = a.`date`) AS `sum2`
FROM
   ("31.05.2013", "30.05.2013", "29.05.2013") AS `date`

But I cant figure out the correct syntax to do this. Is it even possible? And if yes, how?

Upvotes: 0

Answers (3)

Thomas Kekeisen

Reputation: 4406

The tip to use UNION from @Vivek brought on the right way. The solution is:

SELECT
`current_date`,
(SELECT COUNT(*) FROM `subtable1` AS b WHERE b.`date` = `current_date`) AS `sum1`,
(SELECT COUNT(*) FROM `subtable2` AS c WHERE c.`date` = `current_date`) AS `sum2`,
(SELECT COUNT(*) FROM `subtable3` AS d WHERE d.`date` = `current_date`) AS `sum3`
FROM
(
    SELECT  "31.05.2012" AS  `current_date` 
    UNION SELECT  "30.05.2012" AS  `current_date`
    UNION SELECT  "29.05.2012" AS  `current_date`
) AS  `dates`

Edit

So, the final query looks like this and counts all ad clicks, ad impressions and some other stuff for the last 30 days (the timestamps are generated by some php code).

  SELECT
        `current_timestamp`,
        (
            SELECT
                COUNT(*)
            FROM
                `ad_clicks` AS a
            WHERE
                FLOOR(a.`timestamp` / 86400) * 86400 = `current_timestamp`
        ) AS `ad_click_count`,
        (
            SELECT
                COUNT(*)
            FROM
                `ad_impressions` AS b
            WHERE
                FLOOR(b.`timestamp` / 86400) * 86400 = `current_timestamp`
        ) AS `ad_impression_count`,
        (
            SELECT
                COUNT(*)
            FROM
                `stand_touches` AS c
            WHERE
                FLOOR(c.`timestamp` / 86400) * 86400 = `current_timestamp`
        ) AS `stand_touch_count`,
        (
            SELECT
                COUNT(*)
            FROM
                `stand_url_clicks` AS d
            WHERE
                FLOOR(d.`timestamp` / 86400) * 86400 = `current_timestamp`
        ) AS `stand_url_call_count`
    FROM
        (      
            SELECT  "1369958400" AS  `current_timestamp` UNION 
            SELECT  "1369872000" AS  `current_timestamp` UNION 
            SELECT  "1369785600" AS  `current_timestamp` UNION 
            SELECT  "1369699200" AS  `current_timestamp` UNION 
            SELECT  "1369612800" AS  `current_timestamp` UNION 
            SELECT  "1369526400" AS  `current_timestamp` UNION 
            SELECT  "1369440000" AS  `current_timestamp` UNION 
            SELECT  "1369353600" AS  `current_timestamp` UNION 
            SELECT  "1369267200" AS  `current_timestamp` UNION 
            SELECT  "1369180800" AS  `current_timestamp` UNION 
            SELECT  "1369094400" AS  `current_timestamp` UNION 
            SELECT  "1369008000" AS  `current_timestamp` UNION 
            SELECT  "1368921600" AS  `current_timestamp` UNION 
            SELECT  "1368835200" AS  `current_timestamp` UNION 
            SELECT  "1368748800" AS  `current_timestamp` UNION 
            SELECT  "1368662400" AS  `current_timestamp` UNION 
            SELECT  "1368576000" AS  `current_timestamp` UNION 
            SELECT  "1368489600" AS  `current_timestamp` UNION 
            SELECT  "1368403200" AS  `current_timestamp` UNION 
            SELECT  "1368316800" AS  `current_timestamp` UNION 
            SELECT  "1368230400" AS  `current_timestamp` UNION 
            SELECT  "1368144000" AS  `current_timestamp` UNION 
            SELECT  "1368057600" AS  `current_timestamp` UNION 
            SELECT  "1367971200" AS  `current_timestamp` UNION 
            SELECT  "1367884800" AS  `current_timestamp` UNION 
            SELECT  "1367798400" AS  `current_timestamp` UNION 
            SELECT  "1367712000" AS  `current_timestamp` UNION 
            SELECT  "1367625600" AS  `current_timestamp` UNION 
            SELECT  "1367539200" AS  `current_timestamp` UNION 
            SELECT  "1367452800" AS  `current_timestamp`
        ) AS `timestamps`

Upvotes: 1

Stephan

Reputation: 8090

Try this:

SELECT
    `date`,
    SUM(IF(za_type=1,nb,0)) as sum1,
    SUM(IF(za_type=2,nb,0)) as sum2,
    SUM(IF(za_type=3,nb,0)) as sum3
FROM (
    SELECT b.`date`,1 as za_type, COUNT(*) as nb FROM `subtable1` AS b WHERE b.`date` IN ('31-05-2013','30-05-2013','29-05.2013') UNION 
    SELECT c.`date`,2 as za_type, COUNT(*) as nb FROM `subtable2` AS c WHERE c.`date` IN ('31-05-2013','30-05-2013','29-05.2013') UNION 
    SELECT d.`date`,3 as za_type, COUNT(*) as nb FROM `subtable3` AS d WHERE d.`date` IN ('31-05-2013','30-05-2013','29-05.2013') 
) as tmp
GROUP BY
    `date`

UPDATE: if you need for the last 30 days you can add this condition date >= NOW() - INTERVAL 30 DAY instead of date IN (..)

UPDATE2: using new requirement (the query is for the last 3 days):

SELECT
    za_day,
    (SELECT COUNT(*) FROM subtable1 s WHERE s.date = za_day) as sum1,
    (SELECT COUNT(*) FROM subtable2 s WHERE s.date = za_day) as sum2,
    (SELECT COUNT(*) FROM subtable3 s WHERE s.date = za_day) as sum3
FROM (
    SELECT DATE(NOW()) - INTERVAL 1 DAY as za_day UNION
    SELECT DATE(NOW()) - INTERVAL 2 DAY as za_day UNION
    SELECT DATE(NOW()) - INTERVAL 3 DAY as za_day
) as td

Upvotes: 1

glglgl

Reputation: 91149

You could setup a temporary table and fill it with the data you wanted.

Then you can join your present tables with the temporary one as you like.

Upvotes: 0

MySQL: Select from &quot;data list&quot;

Answers (3)

Related Questions

MySQL: Select from "data list"