CODEWITHSUNDEEP
oracle-databaseoracle11g
Siva
Siva

Reputation: 9101

Group by returning 0 rows

My requirement is to show one record when there are no values in the table. I have achieved with following query:

SELECT   TRUNC (SYSDATE - 1) DATE1,
           NVL(AVG (column1),0) col1,
           NVL(AVG (column2),0) col2,
           COUNT ( * ) TOTAL
               FROM   table1
   WHERE column3 = 0  AND TRUNC (UTC_TIMESTAMP + TZ_OFFSET / 24) = TRUNC (SYSDATE - 1) ;

now my problem here is when I add groupby clause to above query result is 0 records are returned.

changed query

My requirement is to show one record when there are no values in the table. I have achieved with following query:

SELECT   TRUNC (SYSDATE - 1) DATE1,
           NVL(AVG (column1),0) col1,
           NVL(AVG (column2),0) col2,
           COUNT ( * ) TOTAL
               FROM   table1
   WHERE column3 = 0  AND TRUNC (UTC_TIMESTAMP + TZ_OFFSET / 24) = TRUNC (SYSDATE - 1) 
GROUP BY   TRUNC (SYSDATE - 1) ;

now I am confused about this behaviour. Why am I getting this strange behaviour?

25-05-2014 0 0 0

Upvotes: 0

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

The explanation is a bit arcane, because aggregation queries have an inconsistency when used with and without a group by clause. Your first query is an aggregation query:

SELECT TRUNC(SYSDATE - 1) as DATE1, NVL(AVG (column1),0) as col1, NVL(AVG (column2),0) as col2,
       COUNT ( * ) TOTAL
FROM   table1
WHERE column3 = 0  AND TRUNC(UTC_TIMESTAMP + TZ_OFFSET / 24) = TRUNC(SYSDATE - 1) ;

This query treats all the matching rows (even none) as a single group. It will always return one row, even when no rows match. This is when COUNT(*) can return 0. By the way, you are allowed to include TRUNC(SYSDATE - 1) because it is a "constant" (relative to the query).

When you add the group by, the semantics change. So this query:

SELECT TRUNC(SYSDATE - 1) as DATE1, NVL(AVG (column1),0) as col1, NVL(AVG (column2),0) as col2,
       COUNT ( * ) TOTAL
FROM table1
WHERE column3 = 0  AND TRUNC(UTC_TIMESTAMP + TZ_OFFSET / 24) = TRUNC(SYSDATE - 1)
GROUP BY TRUNC(SYSDATE - 1) ;

This query returns each group represented by the group by. If there are no groups, then no rows are returned. In this case, all rows are filtered out by the where clause, so no rows are returned.

Upvotes: 1

user3278460
user3278460

Reputation: 

select  TRUNC (SYSDATE - 1) d,
        col1,
        col2,
        total
from (
   SELECT  NVL(AVG (column1),0) col1,
           NVL(AVG (column2),0) col2,
           COUNT (* ) TOTAL
   FROM   table1
   WHERE column3 = 0  AND TRUNC (UTC_TIMESTAMP + TZ_OFFSET / 24) = TRUNC (SYSDATE - 1))
group by TRUNC (SYSDATE - 1),
            col1,
            col2,
            total

Edit---------- added group by to outer query

Upvotes: 0

Related Questions