Jatin Jatin - 1 month ago 7
SQL Question

Display 0 for rows with no data

The Query below displays records for 5 Dashboards. The query works fine. But it displays only those Dashboards for which data exists. What I want to do is, it should display all the Dashboards and the columns for which data does not exist, it should display 0 or NULL.

Select
b.[path],
count(*) as "No of Calls",
a.TimeDataRetrieval as "DB Retrieval time",
a.TimeProcessing as "Processing time",
a.TimeRendering as "Rendering Time"

FROM LogStorage a inner join Catalog b on a.[ReportID] = b.[ItemID]

where b.[path] IN ('Dashboard1','Dashboard2','Dashboard3','Dashboard4','Dashboard5')
group by b.[path]


Thanks.

Answer

The key part is:

FROM LogStorage a inner join Catalog b on a.[ReportID] = b.[ItemID]
where b.[path] IN ('Dashboard1','Dashboard2','Dashboard3','Dashboard4','Dashboard5')

This is restricting the result set to only records that exist in LogStorage and Catalog, irrespective of the join type.

You want to flip this around and use a left-join:

SELECT b.[path]
, SUM (CASE WHEN a.some_column IS NULL THEN 0 ELSE 1 END) as "No of Calls"
, a.TimeDataRetrieval as "DB Retrieval time"
, a.TimeProcessing as "Processing time"
, a.TimeRendering as "Rendering Time"
FROM Catalog b
LEFT JOIN LogStorage a on b.[ItemID] = a.[ReportID]
WHERE b.[path] IN ('Dashboard1','Dashboard2','Dashboard3','Dashboard4','Dashboard5')
group by b.[path]
, a.TimeDataRetrieval
, a.TimeProcessing
, a.TimeRendering

Also, for which table are you counting records? It's a little ambiguous, although if I had to guess I'd say LogStorage. In that case you'll probably want to aggregate those other LogStorage columns, as without an aggregate the output will be just be the rows and the count will be 1 for each row.

Edit: I forgot to mention, that COUNT probably needs to be a SUM CASE expression because null values are eliminated with an aggregate like this.