CODEWITHSUNDEEP
sqloracle-database
jax
jax

Reputation: 840

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

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
.                   .
.                   .
.                   .

Upvotes: 0

Views: 50

Answers (2)

MT0
MT0

Reputation: 168041

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

Upvotes: 2

toonice
toonice

Reputation: 2236

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;

Upvotes: 1

Related Questions