CODEWITHSUNDEEP
sqlpostgresql
Tarik
Tarik

Reputation: 159

Return 0 or Null value if there is no data in a table for a particular date?

I have a database application that stores a series of readings from a particular type of meter.

Suppose that for an individual meter there are no readings on a particular date. Then I query for that meter over a date range. There will be no information for that particular date, which leaves a gap in the displayed output.

So my question is how to return a 0 value for that missing date. For example, consider this code when the meter has no data for 2019-07-02 and 2019-07-03. I'd like to get zeroes as shown in the example output rather than displaying a gap in data.

select m.sdate,
       n.meter_number,
       COALESCE(CAST(n.meter_number as CHAR, 0) AS number_of_cases,
       CASE  WHEN n.count IS NULL THEN '0' ELSE n.count END count
from   (
  SELECT date(dates) as sdate
  FROM   generate_series(
           CAST('2019-07-01' as TIMESTAMP),
           CAST('2019-07-08' as TIMESTAMP),
           interval '1 day'
         ) AS dates
) m
LEFT JOIN (
  SELECT meter_number,
         date(read_time) as rdate,
         COUNT(*)  FROM meter_data.x
  WHERE  date(read_time) BETWEEN '2019-07-01' and  '2019-07-08'
  and    meter_number in ('92589492')
  GROUP BY meter_number,date(read_time)
)n
ON m.sdate=n.rdate ;

2019-07-01  92589492 96
2019-07-02  92589492  0
2019-07-03  92589492  0
2019-07-04  92589492 96
2019-07-05  92589492 96
2019-07-06  92589492 96
2019-07-07  92589492 96
2019-07-08  92589492 96

Upvotes: 1

Views: 1576

Answers (3)

Tarik
Tarik

Reputation: 159

I got My solution.First i generate date series and cross join with meterno which i am passing as a varchar.I use regexp_split_to_table function to display passing meterno as row wise,Then i cross join with date.

Please check my solution.

select m.sdate , COALESCE (m.meterno,'')meter_number,
CASE  WHEN n.count IS NULL THEN '0' ELSE n.count END count
from 
(select date(d) as sdate, mn.meterno 
from  generate_series (CAST('2019-07-01' as TIMESTAMP), CAST('2019-07-06' as TIMESTAMP),  interval '1 day') d
 cross join ( select regexp_split_to_table( '92590714_92590130_92589492_92590381', '_') as meterno) mn)m 
 LEFT JOIN
(SELECT meter_number,date(read_time) as rdate,COUNT(*)  FROM meter_data.load_survey
WHERE date(read_time) BETWEEN '2019-07-01' and  '2019-07-06' and meter_number in ('92590714','92590130','92589492','92590381')  
GROUP BY meter_number,date(read_time) )n
ON m.sdate=n.rdate and m.meterno=n.meter_number
GROUP BY m.sdate,m.meterno,n.count

Upvotes: 1

Morris de Oryx
Morris de Oryx

Reputation: 2195

You've already gotten smarter answers than I can offer, but something about COALESCE caught my eye. If you've got no result coalesce does nothing. Coalesce requires row to work against, so if you have no result, you can't synthetically generate one with a simple COALESCE statement. Here's a simple example, where there is no row with an ID = 1:

-- You get nothing because there is nothing to coalesce against.

select coalesce(foo,'Undefined') from bar where id = 1;

-- You get Undefined because coalesce works here. The nested query returns NULL, which becomes the first argument to coalesce. Since it's NULL and the second argument is 'Undefined', you get back a row with 'Undefined'.

select coalesce(
        (select foo from bar where id = 1),
        'Undefined')

Upvotes: 0

workerjoe
workerjoe

Reputation: 2663

Making some assumptions about the structure of your data (which you can play with in this db-fiddle, a more concise syntax will work. The trick is to start by doing a CROSS JOIN on the dates and meter numbers you're interested in (this essentially creates an empty matrix) and then a LEFT JOIN of all pertinent rows of the meter_data (attaching them to the appropriate matrix cells) and finally aggregate with COUNT() (or just as easily, you could SUM or AVERAGE or MIN or MAX).

In this snippet, I use SELECT DISTINCT meter_number FROM meter_data to get the list of meter numbers I presume we might be interested in:

select to_char(d,'YYYY-MM-DD'), mn.meter_number, count(meter_data.*)
from 
    generate_series ('2019-07-01'::date, '2019-07-06'::date, '1 day') d
    cross join 
    (select distinct meter_number from meter_data) mn
left join meter_data on meter_data.read_time=d and mn.meter_number=meter_data.meter_number
group by d, mn.meter_number
order by mn.meter_number, d;

If you want to look up just a single meter number, you could plug it into the query a couple of ways, for example replace the subquery with SELECT 92589492 as meter_number:

select to_char(d,'YYYY-MM-DD'), mn.meter_number, count(meter_data.*)
from 
    generate_series ('2019-07-01'::date, '2019-07-06'::date, '1 day') d
    cross join 
    (select 92589492 as meter_number) mn
left join meter_data on meter_data.read_time=d and mn.meter_number=meter_data.meter_number
group by d, mn.meter_number
order by mn.meter_number, d;

try it out: db-fiddle

Upvotes: 0

Related Questions