How to view and display in the query the sum even when the value is 0

Question

I am trying to join multiple tables using an oracle sql query and showing the sum of each country from the table "Arab_countries" which has a transaction in the table "CTTRANS"

I am trying to show the sum of transactions by each country. However, the countries that have no transactions are not shown in the output

For example. "AAA" has no transactions, but its not showing in the results of my query. I need it to show as 0

SELECT ARAB_COUNTRIES.COMP_CODE, ARAB_COUNTRIES.COUNTRY_CODE, ARAB_COUNTRIES.SS_CODE, ARAB_COUNTRIES.BRIEF_DESC_ARAB, ARAB_COUNTRIES.LONG_DESC_ENG, ARAB_COUNTRIES.LONG_DESC_ARAB,
SUM(CTTRANS.CV_AMOUNT)
FROM ARAB_COUNTRIES 
 LEFT JOIN CUST 
 ON (ARAB_COUNTRIES.COMP_CODE = CUST.COMP_CODE)
AND (ARAB_COUNTRIES.COUNTRY_CODE = CUST.NATION_CODE)
LEFT JOIN CTTRANS
ON (CTTRANS.COMP_CODE = CUST.COMP_CODE ) 
 AND (CTTRANS.TRS_AC_CUST = CUST.CUST_NO)
    WHERE 
    CTTRANS.STATUS = 'P' AND CTTRANS.TRX_TYPE IN (201,15,35,586) 
    AND TRUNC(CTTRANS.TRS_DATE) BETWEEN '01-APR-20' AND  '30-JUN-20'
    AND ARAB_COUNTRIES.COUNTRY_CODE <> 999
   
 GROUP BY ARAB_COUNTRIES.COMP_CODE, ARAB_COUNTRIES.COUNTRY_CODE, ARAB_COUNTRIES.SS_CODE, ARAB_COUNTRIES.BRIEF_DESC_ARAB, ARAB_COUNTRIES.LONG_DESC_ENG, ARAB_COUNTRIES.LONG_DESC_ARAB
ORDER BY ARAB_COUNTRIES.COUNTRY_CODE;

is the "where" in your left join filtering out the countries? https://stackoverflow.com/questions/4752455/left-join-with-where-clause — Neville Kuyt, Jul 23 '20 at 09:28
Nope its not filtering out the country. The country has no transactions therefore i want it to show as 0 instead of not appearing in the sql result at all — ahmed, Jul 23 '20 at 09:35
Sorry, I meant the transactions. The where clause on your left join to transactions filters out any results without transactions, and therefore the left join doesn't include those countries. — Neville Kuyt, Jul 23 '20 at 09:45
It would make it easier to answer if you could provide ddl for the tables and dml for some sample data... that way we can reproduce your problem in seconds. Now it is a bit of guessing. — Koen Lostrie, Jul 23 '20 at 10:03
@Ahmed If you do not want to share sample data then kindly provide information in plain english this is good for SQL QUERY. example:: Imagine that we run an online bookstore, and we want to compare the customers with orders in the last 6 months with those who were inactive during the same period. In this case, we want the result to include ALL customers–both active and inactive–by joining the data about our customers with the data about recent sales. — Nadeem Taj, Jul 23 '20 at 10:23

score 1 · Answer 1 · answered Jul 23 '20 at 10:45

Your WHERE clause is undoing the outer joins. Move the conditions on all but the first table to the ON clauses:

SELECT ac.COMP_CODE, ac.COUNTRY_CODE, ac.SS_CODE, ac.BRIEF_DESC_ARAB, ac.LONG_DESC_ENG, ac.LONG_DESC_ARAB,
       SUM(t.CV_AMOUNT)
FROM ARAB_COUNTRIES ac LEFT JOIN
     CUST c
     ON ac.COMP_CODE = c.COMP_CODE AND
        ac.COUNTRY_CODE = c.NATION_CODE LEFT JOIN
     CTTRANS t
     ON t.COMP_CODE = c.COMP_CODE AND
        t.TRS_AC_CUST = c.CUST_NO AND
        t.STATUS = 'P' AND
        t.TRX_TYPE IN (201, 15, 35, 586) AND
        TRUNC(t.TRS_DATE) BETWEEN DATE '2020-04-01' AND DATE '2020-06-30'
WHERE ac.COUNTRY_CODE <> 999
GROUP BY ac.COMP_CODE, ac.COUNTRY_CODE, ac.SS_CODE, ac.BRIEF_DESC_ARAB, ac.LONG_DESC_ENG, ac.LONG_DESC_ARAB
ORDER BY ac.COUNTRY_CODE;

Note the use of table aliases. They make the query easier to write and to read.

Note that I changed the date constants to use the standard method for introducing them. I would actually recommend removing the TRUNC() as well:

        t.TRS_DATE >= DATE '2020-04-01' AND
        t.TRS_DATE < DATE '2020-07-01'

This is better for the optimizer -- both in terms of indexes and table statistics.

Atif · Answer 2 · 2020-07-24T07:18:11.767

Its better if you paste your DDL and some sample data in these tables.

But I think looking at the information available issue is that if the there no transaction in Transaction in CTTRANS means no matching row is present then data will be null and hence you should use SUM(NVL(CTTRANS.CV_AMOUNT, 0)) in 2nd line of your code and check the result.

I have executed below two queries on Livesql.oracle.com

1st Query will give salary as blank

SELECT A.DEPARTMENT_ID, SUM(B.SALARY) FROM HR.DEPARTMENTS A LEFT JOIN HR.EMPLOYEES B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
WHERE A.DEPARTMENT_ID = 120 GROUP BY A.DEPARTMENT_ID ;

2nd Query will give Salary as 0 after using NVL

SELECT A.DEPARTMENT_ID, SUM(NVL(B.SALARY, 0)) FROM HR.DEPARTMENTS A LEFT JOIN HR.EMPLOYEES B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
WHERE A.DEPARTMENT_ID = 120 GROUP BY A.DEPARTMENT_ID ;

Please share the feedback and if issue is not resolved share the details as requested.

How to view and display in the query the sum even when the value is 0

2 Answers2