0

I am querying in DB for last 72 records in Db in my Db it may happens all data will not be present for that I want to add dummy row in which I will add rank and adding null value to remaining data.

Query:

select (@rownum := @rownum + 1) AS rank, 
date, day(date), hour(date),Sum(Ifnull(impressions, 0)) AS 
total_Impressions,
ROUND(Sum(Ifnull(clicks, 0)) * 100.0 / Sum(Ifnull(impressions, 0)),10)  
AS CTR_percentage,
ROUND(Sum(Ifnull(dv_viewed, 0)) * 100.0 / Sum(Ifnull(dv_measured, 
0)),1)    AS Viewability_percentage,
ROUND(Sum(Ifnull(dv_measured, 0)) * 100.0 / Sum(Ifnull(dv_impression, 
0)),1) AS Measurability_percentage
FROM (SELECT @rownum := 0) t,rhino.delivery_snapshot_summary where 
adgroup_id IN(SELECT id FROM ad_group where campaign_id = 2467 AND 
bundle = 0)
and date <='2018-11-26 23:59:59' and date >'2018-11-22 23:59:59'  group 
by day(date), hour(date) order by date DESC limit 72;

and I'm getting following response : enter image description here

I want total 72 rows with all null data in response.

Database : Mysql
using MySQL workbench 6.3.6
mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using EditLine wrapper

0

1 Answer 1

1

Add 72 records with all fields NULL, get first 72 records (you sort in DESC, so NULLs will be last).

( SELECT ..... 
  ........
  order by date DESC limit 72 ) 
UNION ALL 
( SELECT x.*
  FROM ( SELECT null, null, ... null ) x,
       ( SELECT 1 UNION SELECT 2 ... UNION SELECT 72 ) y
)
order by date DESC 
/* , COALESCE('all another resultset fields list') IS NULL */ 
limit 72

COALESCE needed if date field can be NULL.

PS. Pseudotable with numbers from 1 to 72 can be generated using 2-3-4 small tables. Or in recursive CTE if DBMS allows...

PPS. In recursive CTE you can generate the required number of records up to 72 total records at once.

7
  • sir Do i need to write null and select 1/2/3 72 times??? for those 3 dots? Dec 11, 2018 at 9:53
  • @indrajitnarvekar The count of NULLs in x subquery must match the count of fields in your query used as first subquery (or UNION will fail). 3 dots in y subquery means that you must generate a fake table containing 72 (at least) records with any (I use numbers) data.
    – Akina
    Dec 11, 2018 at 9:57
  • I'm getting Error Code :1060 Duplicate column name 'NULL' Dec 11, 2018 at 10:17
  • @indrajitnarvekar it's a problem? Add unique (any - doesn't matter) names to each null-valued field in x subquery. And maybe do the same for first value (1) in y subquery.
    – Akina
    Dec 11, 2018 at 10:19
  • I made changes as per your comment but now I'm getting Error Code: 1222. The used SELECT statements have a different number of columns Dec 11, 2018 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.