show zero sum in sql query

20 pts.
Tags:
SQL Query
i want to display for all regions sum value. The region 4 is 0 but it is not displaying 0
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

the oracle version is 9.2.0.1.0

The sql is like this

SELECT c.region_no,
decode(c.deemed,0,’No’,’Yes’) deemed,
sum(decode(arb.property_code.res_nonres,’1′,1,0)) as “Residential”,
sum(decode(arb.property_code.res_nonres,’0′,1,’2′,1,’3′,1,0)) as “Non_Residential”

FROM “ARB”.”HEARING”,
“ARB”.”HEARING_COMPLAINTS”,
ARB.”COMPLAINT” “C”,
ARB.PROPERTY_CODE

WHERE ( “ARB”.”HEARING”.”HEARING_NO” = “ARB”.”HEARING_COMPLAINTS”.”HEARING_NO” ) and
(“ARB”.”HEARING_COMPLAINTS”.”COMPLAINT_NO” = c.complaint_no) and
(ARB.PROPERTY_CODE.PROPERTY_CODE = C.PROPERTY_CODE) and
(c.section_no = any(’33’, ’34’, ’40’)) and
( ARB.”HEARING”.”START_DATE_TIME” < to_char(’12/14/2008′) ) and
(arb.hearing.hearing_type = ‘H’) and
(substr(c.”PRES_HEARING_NO”,1,1) NOT in (‘R’,’C’,’D’))

group by c.region_no,
decode(c.deemed,0,’No’,’Yes’)

The result is like this

01 No 10 0
02 No 3 3
02 Yes 2 6
03 No 4 7
03 Yes 2 4
05 No 1 0
06 No 3 20
06 Yes 4 27

It should show like this

01 No 10 0
01 Yes 0 0
02 No 3 3
02 Yes 2 6
03 No 4 7
03 Yes 2 4
04 No 0 0
04 Yes 0 0
05 No 1 0
05 Yes 0 0
06 No 3 20
06 Yes 4 27

i have used nvl function or decode to get value of 0

————–

Answer:

It seems that you want to show 0 for regions
that do not have any corresponding row in ARB.PROPERTY_CODE. If that’s
true, you would need to use an outer join instead, and add a nvl in the
sum function.

Something like this:

 SELECT c.region_no,
  DECODE(c.deemed,0,'No','Yes') deemed,
  SUM(DECODE(NVL(arb.property_code.res_nonres,-1),'1',1,0))             AS "Residential",
  SUM(DECODE(NVL(arb.property_code.res_nonres,-1),'0',1,'2',1,'3',1,0)) AS "Non_Residential"
FROM "ARB"."HEARING",
  "ARB"."HEARING_COMPLAINTS",
  ARB."COMPLAINT" "C",
  ARB.PROPERTY_CODE
WHERE ( "ARB"."HEARING"."HEARING_NO"           = "ARB"."HEARING_COMPLAINTS"."HEARING_NO" )
AND ("ARB"."HEARING_COMPLAINTS"."COMPLAINT_NO" = c.complaint_no)
AND (ARB.PROPERTY_CODE.PROPERTY_CODE (+)       = C.PROPERTY_CODE)
AND (c.section_no                              = ANY('33', '34', '40'))
AND ( ARB."HEARING"."START_DATE_TIME"          < TO_CHAR('12/14/2008') )
AND (arb.hearing.hearing_type                  = 'H')
AND (SUBSTR(c."PRES_HEARING_NO",1,1) NOT      IN ('R','C','D'))
GROUP BY c.region_no,
  DECODE(c.deemed,0,'No','Yes');


Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    Please provide more information: - What database are you using ? - What field are you summing ? - What is your table structure ? - What sql statement are you using ?
    84,745 pointsBadges:
    report
  • carlosdl
    It seems that you want to show 0 for regions that do not have any corresponding row in ARB.PROPERTY_CODE. If that's true, you would need to use an outer join instead, and add a nvl in the sum function. Something like this: SELECT c.region_no, decode(c.deemed,0,'No','Yes') deemed, sum(decode(nvl(arb.property_code.res_nonres,-1),'1',1,0)) as "Residential", sum(decode(nvl(arb.property_code.res_nonres,-1),'0',1,'2',1,'3',1,0)) as "Non_Residential" FROM "ARB"."HEARING", "ARB"."HEARING_COMPLAINTS", ARB."COMPLAINT" "C", ARB.PROPERTY_CODE WHERE ( "ARB"."HEARING"."HEARING_NO" = "ARB"."HEARING_COMPLAINTS"."HEARING_NO" ) and ("ARB"."HEARING_COMPLAINTS"."COMPLAINT_NO" = c.complaint_no) and (ARB.PROPERTY_CODE.PROPERTY_CODE (+) = C.PROPERTY_CODE) and (c.section_no = any('33', '34', '40')) and ( ARB."HEARING"."START_DATE_TIME" < to_char('12/14/2008') ) and (arb.hearing.hearing_type = 'H') and (substr(c."PRES_HEARING_NO",1,1) NOT in ('R','C','D')) group by c.region_no, decode(c.deemed,0,'No','Yes')
    84,745 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: