Reputation: 8790
how to use Union all query for fetching the records which is not in table?
i have a_ad_display table in MYSQL Database with the following format
╔═════════════╦═════════╦═════════╦═════════════════════╗
║ addisplayId ║ restId ║ clicked ║ displaytime ║
╠═════════════╬═════════╬═════════╬═════════════════════╣
║ 1 ║ 20707 ║ 0 ║ 2013-03-07 10:50:55 ║
║ 2 ║ 20707 ║ 1 ║ 2013-03-07 10:55:55 ║
║ 3 ║ 20711 ║ 1 ║ 2013-03-08 12:50:55 ║
║ 4 ║ 20712 ║ 1 ║ 2013-03-08 01:50:55 ║
║ 5 ║ 20707 ║ 1 ║ 2013-03-08 10:50:55 ║
║ 6 ║ 20708 ║ 1 ║ 2013-03-09 12:50:55 ║
║ 7 ║ 20707 ║ 1 ║ 2013-03-09 09:50:55 ║
║ 8 ║ 20708 ║ 0 ║ 2013-03-10 10:50:55 ║
║ 9 ║ 20708 ║ 0 ║ 2013-03-11 11:50:55 ║
║ 10 ║ 20707 ║ 0 ║ 2013-03-11 12:50:55 ║
╚═════════════╩═════════╩═════════╩═════════════════════╝
Now what i want is to fetch records of the restId with the last 7 days data
if current date is 2013-3-12 then i want to show data of 2013-3-5 to 2013-3-11
DISPLAY TIME CLICKED count of row(impression)
March, 05 2013 00:00:00+0000 0 0
March, 06 2013 00:00:00+0000 0 0
March, 07 2013 00:00:00+0000 1 2
March, 08 2013 00:00:00+0000 1 1
March, 09 2013 00:00:00+0000 1 1
March, 10 2013 00:00:00+0000 1 1
March, 11 2013 00:00:00+0000 0 1
i want to ignore timing..just want to focus on date while fetching record date wise i want to fetch the record with restId=10707 with the total count of row for that date in table and the sum of clicks for that date and if there is no record in the table for any date which coming in in last week's date then it should print that date and print 0 in clicked and also in row count column with 0.
i have made one dummy table in SQL fiddle and also applied this query but its not working..so anyone can please guide me...i know its very complex to understand..but if you have any question about my problem..then will clear u to get a solution from your side..
following is my query which not giving proper data
select
displaytime,
sum(impression) as clicked,
(count(impression) -1) as impressions
from (
select
displaytime,
a_ad_display.clicked as impression
from
a_ad_display
where
a_ad_display.displaytime
BETWEEN
date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -6 day)
AND
CONVERT_TZ(CURDATE( ),'+00:00','-11:00')
and
restId=10708
group by
a_ad_display.displaytime
union distinct
select
CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), 0
union distinct
select
date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -1 day), 0
union distinct
select
date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -2 day), 0
union distinct
select
date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -3 day), 0
union distinct
select
date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -4 day), 0
union distinct
select
date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -5 day), 0
union distinct
select
date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -6 day), 0
) as x
group by
date(displaytime)
order by
displaytime
please ignore the code CONVERT_TZ(CURDATE( ),'+00:00','-11:00') beacuse its just convert my date to user time zone from utc...
here is my sql fiddle link http://sqlfiddle.com/#!2/73f07/1
Upvotes: 1
Views: 349
Answers (4)
Reputation: 4412
This query should display the correct data. Not a very 'clean' solution. You should replace those unions with a calendar table if you really want to include dates that have no data.
SELECT IFNULL(z.displaytime, x.displaytime), IFNULL(z.clicked, 0), IFNULL(z.count, 0) FROM
(select
date(CONVERT_TZ(CURDATE( ),'+00:00','-11:00')) AS 'displaytime', 0 AS 'Clicked', 0 AS 'count of row(impression)'
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -1 day)), 0, 0
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -2 day)), 0, 0
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -3 day)), 0, 0
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -4 day)), 0, 0
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -5 day)), 0, 0
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -6 day)), 0, 0) AS x
LEFT JOIN
(SELECT
DATE(displaytime) AS 'displaytime',
MAX(clicked) AS 'Clicked',
COUNT(*) AS 'Count'
FROM
a_ad_display
WHERE (
displaytime BETWEEN date_add(CURDATE(), interval -6 day) AND CURDATE()
) AND restId = 10707
GROUP BY DATE(displaytime)) AS z
ON x.displaytime = z.displaytime
Upvotes: 2
Reputation: 9724
Some other solution for restID 20707. For 2013-03-10 there is no record for restID 20707.
Query SQLFIDDLEExample:
SELECT d.Date,
(SELECT CASE WHEN COUNT(clicked) >0 THEN 1 ELSE 0 END
FROM a_ad_display
WHERE DATE(displaytime) = d.Date
AND clicked = 1
AND restId = 20707 ) Clicked,
(SELECT COUNT(*)
FROM a_ad_display
WHERE DATE(displaytime) = d.Date
AND restId = 20707 ) 'count of row'
FROM
(SELECT a.Date
FROM
(SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Date
FROM
(SELECT 0 AS a
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9) AS a CROSS
JOIN
(SELECT 0 AS a
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9) AS b CROSS
JOIN
(SELECT 0 AS a
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9) AS c) a
where a.Date >= '2013-01-01' and a.Date < curdate()) d
WHERE d.Date >= date_add(curdate(), INTERVAL -7 DAY)
ORDER BY d.Date
Result:
| DATE | CLICKED | COUNT OF ROW |
---------------------------------------------------------
| March, 05 2013 00:00:00+0000 | 0 | 0 |
| March, 06 2013 00:00:00+0000 | 0 | 0 |
| March, 07 2013 00:00:00+0000 | 1 | 2 |
| March, 08 2013 00:00:00+0000 | 1 | 1 |
| March, 09 2013 00:00:00+0000 | 1 | 1 |
| March, 10 2013 00:00:00+0000 | 0 | 0 |
| March, 11 2013 00:00:00+0000 | 0 | 1 |
Upvotes: 1
Reputation: 771
There's probably a cleaner way, but this should work...
select
displaytime,
sum(clicked) as clicked,
sum(impressions) as impressions
from (
select
displaytime,
clicked,
impressions
from (
select
date(displaytime) as displaytime,
sum(impression) as clicked,
sum(legit) as impressions
from (
select
displaytime,
a_ad_display.clicked as impression,
1 as legit
from
a_ad_display
where
a_ad_display.displaytime
BETWEEN
date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -6 day)
AND
CONVERT_TZ(CURDATE( ),'+00:00','-11:00')
and
restId=10708
) as x
group by
date(displaytime)
) y
union distinct
select
date(CONVERT_TZ(CURDATE( ),'+00:00','-11:00')), 0, 0
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -1 day)), 0, 0
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -2 day)), 0, 0
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -3 day)), 0, 0
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -4 day)), 0, 0
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -5 day)), 0, 0
union distinct
select
date(date_add(CONVERT_TZ(CURDATE( ),'+00:00','-11:00'), interval -6 day)), 0, 0
) z
group by
displaytime
order by
displaytime
Upvotes: 1
Reputation: 14728
Have a look at this question:
It's almost identical to what you're doing
Upvotes: 0
Related Questions
- How to select all records from a table that do not exist in a union of two another tables in mysql
- How to fetch record from another table if first table return no record using UNION in MySQL query
- Union All returns value which has no record in table
- UNION ALL query missing data?
- mySQL: Get records union from same table
- sql union with not in condition
- MySql select by excluding all id from another table
- Mysql:Avoid Nulls in Union All query
- Select all which is not in table
- Exclude null results on a UNION query