SQL query return a result on group by expression (Oracle)

Question

When I run the following query, I get a sum of amount per accounting period. However in some accounting periods, I do not have any amount, so naturally, those periods with no sum of amount (null) are not shown in the result set.

I would like to know how to return those with the sum of amount 0 for them.

SELECT 
    ACCOUNTING_PERIOD, 
    SUM(RESOURCE_AMOUNT) AS TOTAL,
FROM 
    RESOURCE_TBL
GROUP BY
    ACCOUNTING_PERIOD

I get the following result set

accounting_period   TOTAL
-------------------------
1                   234
3                    65
5                   943
6                   299
.                   .
.                   .
.                   .

In the above period 2 and 4 is left out since the sum is zero or null but I would like to get

accounting_period   TOTAL
-------------------------
1                   234
2                     0
3                    65
4                     0
5                   943
6                   299
.                   .
.                   .
.                   .

Does the above mean that periods 2 and 4 have rows in RESOURCE_TBL with a value of NULL for RESOURCE_AMOUNT or that they do not have rows at all? — toonice, Mar 29 '17 at 14:48
@toonice - it means no rows at all. If there were rows with NULL in them, then the result would have rows for those accounting periods, with the sum shown as NULL. — , Mar 29 '17 at 16:07

score 2 · Accepted Answer · answered Mar 29 '17 at 14:39

Generate a list of all the possible accounting periods and then LEFT OUTER JOIN that with your table:

WITH accounting_periods ( accounting_period ) AS (
  SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 12
)
SELECT a.ACCOUNTING_PERIOD, 
       COALESCE( SUM(RESOURCE_AMOUNT), 0 ) AS TOTAL
FROM   accounting_periods a
       LEFT OUTER JOIN
       RESOURCE_TBL r
       ON ( a.accounting_period = r.accounting_period )
GROUP BY a.ACCOUNTING_PERIOD

score 1 · Answer 2 · edited May 23 '17 at 12:10

1

As per the article How do I get SUM function in MySQL to return '0' if no values are found?, please try the following...

SELECT ACCOUNTING_PERIOD,
       COALESCE( SUM(RESOURCE_AMOUNT), 0 ) AS TOTAL
FROM RESOURCE_TBL
GROUP BY ACCOUNTING_PERIOD;

edited May 23 '17 at 12:10

Community

1
1

answered Mar 29 '17 at 14:36

toonice

2,211
1
13
20

1

This will not get a zero value for an accounting period if there are no rows in the table with that accounting period. – MT0 Mar 29 '17 at 14:43
The phrase jax used of "those periods with no sum of amount (null)" suggested to me that RESOURCE_TBL does indeed have rows for such accounting periods with NULL being the value of RESOURCE_AMOUNT. I shall ask Jax to clarify. – toonice Mar 29 '17 at 14:47
At the time I posted the question I was not sure if the table had rows with all accounting periods. – jax Mar 29 '17 at 14:56

SQL query return a result on group by expression (Oracle)

2 Answers2