Display number zero, when it has no data

Question

I'm doing a search on some tables, I want to calculate the result of a table, and when it does not have data on the date I set, I want to show the number zero.

SELECT 
    l.key as "Licença",
    coalesce(sum(c."customersAmount"), 0) as "Comsumo dos ultimos 30 dias"
FROM
    consumptions c
LEFT JOIN licenses l on c."licenseKey"=l.key
WHERE 
    c."consumedAt" >= current_date - interval '30' day AND
    l.label LIKE '%Casa%'
GROUP BY 
    l.key
ORDER BY 
    l.key ASC;

The lkey that has no consumption, I want to display the number zero (0).

how can I do this ? thank you

Possible duplicate of [SQL Server: How to select all days in a date range even if no data exists for some days](https://stackoverflow.com/questions/5899829/sql-server-how-to-select-all-days-in-a-date-range-even-if-no-data-exists-for-so) — Eric Brandt, Apr 24 '19 at 21:01
Or this. https://dba.stackexchange.com/questions/72419/filling-in-missing-dates-in-record-set-from-generate-series. The point is, you need to generate a list of dates to account for the ones that aren't in your data. This question has been asked and answered for every flavor of sql many times. — Eric Brandt, Apr 24 '19 at 21:08

score 0 · Answer 1 · answered Apr 24 '19 at 21:02

0

You can use

CASE WHEN l.key NULL THEN 'Empty' ELSE l.key END as "Licença"

or

coalesce(l.key, 'Empty') AS "Licença"

answered Apr 24 '19 at 21:02

Jhonatan Piero Abarca Murillo

81
3

If a given date doesn't appear in the data, it doesn't through a `NULL` to `COALESCE` against. There just isn't a record there at all, and this won't help with that. – Eric Brandt Apr 24 '19 at 21:09

score 0 · Answer 2 · answered Apr 24 '19 at 21:10

You should move c."consumedAt" >= current_date - interval '30' day from the WHERE to the ON clause. In the WHERE it filters all row out, where no row from licenses were joined as "consumedAt" is null for them which is never larger than or equal to any other value.

SELECT l.key "Licença",
       coalesce(sum(c."customersAmount"), 0) "Comsumo dos ultimos 30 dias"
       FROM consumptions c
            LEFT JOIN licenses l
                      ON c."licenseKey" = l.key
                         AND c."consumedAt" >= current_date - interval '30' day
       WHERE l.label LIKE '%Casa%'
       GROUP BY l.key
       ORDER BY l.key ASC;

score 0 · Accepted Answer · answered Apr 24 '19 at 21:16

0

I think you want the licenses table as the first table in the LEFT JOIN, so I think this is the query you want:

SELECT l.key as "Licença",
       coalesce(sum(c."customersAmount"), 0) as "Comsumo dos ultimos 30 dias"
FROM licenses l LEFT JOIN
     consumptions c
     ON c."licenseKey" = l.key AND
        c."consumedAt" >= current_date - interval '30' day
WHERE l.label LIKE '%Casa%'
GROUP BY l.key
ORDER BY l.key ASC;

answered Apr 24 '19 at 21:16

Gordon Linoff

1,242,037
58
646
786

I think that's exactly what you said, I'll try. – 2kBIT Apr 25 '19 at 11:23

Display number zero, when it has no data

3 Answers3