Return 0 total using Grouping Set

Question

The below query works perfectly when there are rows. Output as:

   Duplicates,2
   Syntax,5
   Total,7

However I need to return a row of Total, 0 when there are no rows.

Have tried changing the INNER JOIN to a RIGHT JOIN but this then returns two rows. Total,0 Total,0

I probably could work with that and strip the extra one in my VB code but would like to work out how to do it within the SQL.

    SELECT
       nvl(to_char(dbms_lob.substr(message, 50, 1 )),'Total') AS TYPE ,
       Count(dbms_lob.substr( message, 50, 1 )) AS "HOWMANY"
   FROM applicationlogentries ALE
   INNER JOIN (
            SELECT REFERENCE_ID , Max(entry_date) AS MaxDateTime
            FROM APPLICATIONLOGENTRIES
            where Trunc(entry_date) = Trunc(SYSDATE) -8
            GROUP BY  REFERENCE_ID) groupedAle
            ON ale.reference_id = groupedAle.reference_id
            AND ale.last_updated = groupedAle.MaxDateTime
            AND ale.reference_id IN
                   (SELECT ID FROM documentsin
                    where Trunc(date_received) = Trunc(SYSDATE) -8 AND 
          status = 3)
       group by grouping sets((),(dbms_lob.substr( message, 50, 1 )))

score 1 · Answer 1 · answered Jan 11 '19 at 16:25

This is too long for a comment.

If you run:

select count(*)
from t;

Then you will always get one row, even if t is empty. That is the definition of an aggregation over the whole table.

If you run:

select count(*)
from t
group by <whatever>

Then you will get one row per group. If there are no groups (i.e. no data in the table), you get no rows.

I can think of no way to return a row -- in this case -- on an empty table, using group by.

Apparently,

group by grouping sets ( () )

behaves like the second case and not the first. It is saying "there is a group by, so one row per group" and "there is no data so there are no groups".

The only solution I can readily think of is the brute force solution: union all for the total that you want to calculate.

Hi Gordon, yes I pretty much expected that, but thought i'd give it a shot on here. Will leave it open a couple of days in case someone has a weird and wonderful solution. I have actually coded around it in VB, by saying if the rowcount variable returned is empty then there were no rows. — DanBot, Jan 11 '19 at 17:04

score 0 · Answer 2 · answered Jan 11 '19 at 17:11

You need to perform a left outer join on your data to get the count of zero to return. I have found this page that helped me understand how to test this. I created a couple of dummy tables. It appears you must have records in one of your joined tables if you expect the count(*) to give you a zero value.

How to include "zero" / "0" results in COUNT aggregate?

"The reason why this is working, is that the outer (left) join will return NULL for those persons that do not have an appointment. The aggregate function count() will not count NULL values and thus you'll get a zero."

 Select a.colA, count(b.ColA)

from [dbo].[SRJTestTable1] as a
Join [dbo].[SRJTestTable2] as b
ON a.ColA =  b.ColA
Group by a.ColA

Assuming that table 1 has records and table 2 does NOT and you want to count the values from table 2 you will get nothing returned on an inner join. If you change to a left outer join the result will be a zero.

I have run this with and without the outer join and I did get a count of zero to return with the outer join.

Couldn't get my image to appear in-line. Here is a quick link to it.

https://i.stack.imgur.com/1abv6.jpg

Hi Steven, with LEFT OUTER, I get lots of rows I do not want, had already tried that. Thanks anyway. — DanBot, Jan 11 '19 at 21:55

Return 0 total using Grouping Set

2 Answers2