Mysql – How To return null value if no records present in select query

MySQL

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

Best Answer

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.

Related Question