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

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.

  • sir Do i need to write null and select 1/2/3 72 times??? for those 3 dots? – indrajit narvekar Dec 11 '18 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 '18 at 9:57
  • I'm getting Error Code :1060 Duplicate column name 'NULL' – indrajit narvekar Dec 11 '18 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 '18 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 – indrajit narvekar Dec 11 '18 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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