Oracle Decode Adding Count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Oracle Decode Adding Count

Author

Topic

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-14 : 21:15:59

This code gives me the Person_ID,NAB_COUNT,TERM_001_CNT,TERM_002_CNT.

I am trying to add an entire count from the TERMS.
So I would like a seperate columns that Counts the Combined Terms.
Ie if TERM_001 is 2 and TERM_002 is 5. The new column should read 7

I tried using innner join just like i did on the --Provider Count
But i get missing right perenthesis.

 SELECT 
 PERSON_ID
,NAB_COUNT

,MAX( DECODE( TERM, 0001, TERM_COUNT, null ) ) "TERM_0001_CNT"
,MAX( DECODE( TERM, 0002, TERM_COUNT, null ) ) "TERM_0002_CNT"

FROM(
--PROVIDER COUNT
SELECT Distinct
a.PERSON_id, a.PROVIDER_CDE, a.NAB_COUNT,c.TERM as TERM,c.TERM_COUNT
FROM
(SELECT *
FROM
(SELECT PERSON_id, PROVIDER_CDE, count(*) NAB_COUNT
FROM NAB_enrolment_mv 
WHERE Provider_cde = 'NAB'  
GROUP BY PERSON_id, PROVIDER_CDE))A

--TERM COUNT
INNER JOIN
(SELECT *
FROM(
SELECT PERSON_ID, TERM,PROVIDER_CDE, count(*) TERM_COUNT
FROM NAB_enrolment_mv 
WHERE Provider_cde = 'NAB'
GROUP BY PERSON_ID, TERM, PROVIDER_CDE))c
ON A.PERSON_ID = c.PERSON_ID
AND A.PROVIDER_CDE = c.PROVIDER_CDE
ORDER BY PERSON_ID ASC) 
GROUP BY PERSON_ID, NAB_COUNT
ORDER BY PERSON_ID ASC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-14 : 21:57:22

why don't you post in an Oracle forum ?

anyway, if the error is missing right parenthesis, just check your code and make sure you have matching left and right parenthesis

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-14 : 22:44:22

Got rid of the error.
I added this Count to it.

INNER JOIN
(
SELECT *
FROM
(SELECT PERSON_id, TERM, count(*) TERM_COUNTS 
FROM NAB_enrolment_mv 
WHERE Provider_cde = 'NAB'  
GROUP BY PERSON_id, TERM))D
ON D.PERSON_ID = C.PERSON_ID

But it is counting each term individualy insted of combined.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-14 : 22:52:56

this ?


SELECT PERSON_id, TERM, count(*) TERM_COUNTS 
FROM   NAB_enrolment_mv 
WHERE  Provider_cde = 'NAB'  
GROUP BY PERSON_id, TERM

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-14 : 23:31:44

No, that does not count TERM_001 and TERM_002 ALONE.
Insted it counts all terms and keeps adding to them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-14 : 23:41:33

Don't quite understand what you are doing here, can you post your table schema, sample data and expected result ?

If you are using ORACLE, you really should be posting in an Oracle forum. The solution we provided here will works on SQL Server and not necessary applicable to Oracle

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-14 : 23:58:09

Does this help?

Here is what i have.

       ID      NAB_CNT    TERM 603   TERM 703 
100000	  1	   null	       null
100009	  6	    2	        1

I want this

       ID      NAB_CNT   TERM_CNT      TERM 603   TERM 703 
100000	  1	    null          null	       null
100009	  6	     3             2	        1

Just want an extra Colum (TERM_CNT) which combines the Terms 603,703 etc...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-15 : 00:07:58

you don't need another inner join to get that.

Just do it in the SELECT clause


           MAX( DECODE( TERM, 0001, TERM_COUNT, null ) )
         + MAX( DECODE( TERM, 0002, TERM_COUNT, null ) ) "TERM_CNT"

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-15 : 00:14:21

Not sure where you mean I should put that.
Could you how me in the code.

SELECT 
 PERSON_ID
,NAB_COUNT

,MAX( DECODE( TERM, 0001, TERM_COUNT, null ) ) "TERM_0001_CNT"
,MAX( DECODE( TERM, 0002, TERM_COUNT, null ) ) "TERM_0002_CNT"

FROM(
--PROVIDER COUNT
SELECT Distinct
a.PERSON_id, a.PROVIDER_CDE, a.NAB_COUNT,c.TERM as TERM,c.TERM_COUNT
FROM
(SELECT *
FROM
(SELECT PERSON_id, PROVIDER_CDE, count(*) NAB_COUNT
FROM NAB_enrolment_mv 
WHERE Provider_cde = 'NAB'  
GROUP BY PERSON_id, PROVIDER_CDE))A

--TERM COUNT
INNER JOIN
(SELECT *
FROM(
SELECT PERSON_ID, TERM,PROVIDER_CDE, count(*) TERM_COUNT
FROM NAB_enrolment_mv 
WHERE Provider_cde = 'NAB'
GROUP BY PERSON_ID, TERM, PROVIDER_CDE))c
ON A.PERSON_ID = c.PERSON_ID
AND A.PROVIDER_CDE = c.PROVIDER_CDE
ORDER BY PERSON_ID ASC) 
GROUP BY PERSON_ID, NAB_COUNT
ORDER BY PERSON_ID ASC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-15 : 00:20:15

[code]
SELECT
PERSON_ID
,NAB_COUNT
,MAX( DECODE( TERM, 0001, TERM_COUNT, null ) )
+MAX( DECODE( TERM, 0002, TERM_COUNT, null ) ) "TERM_CNT"
,MAX( DECODE( TERM, 0001, TERM_COUNT, null ) ) "TERM_0001_CNT"
,MAX( DECODE( TERM, 0002, TERM_COUNT, null ) ) "TERM_0002_CNT"

FROM(
--PROVIDER COUNT
[/code]

i am not sure what MAX(DECODE . .) returns, i am assuming it is numeric

also you can remove the JOIN to the TERM COUNT query

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-15 : 00:25:33

You are a Legend.
Thank you so so much.

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-15 : 00:35:47

Sorry just another question before i close this topic if you have time.

If i wanted to get another column next to the Term one you just created which counts Percentage with respect to a term, how would i do that.

So something like this:

ID      NAB_CNT   TERM_CNT  TERM % 603     TERM 603   TERM 703 
100000	  1	    null       NULL          null      null
100009	  6	     3          67%          2	       1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-15 : 00:38:50

[code]
SELECT
PERSON_ID
,NAB_COUNT
,MAX( DECODE( TERM, 0001, TERM_COUNT, null ) )
+MAX( DECODE( TERM, 0002, TERM_COUNT, null ) ) "TERM_CNT"
,MAX( DECODE( TERM, 0001, TERM_COUNT, null ) ) * 100.0 /
(MAX( DECODE( TERM, 0001, TERM_COUNT, null ) )
+MAX( DECODE( TERM, 0002, TERM_COUNT, null ) ) ) "TERM % 603"
,MAX( DECODE( TERM, 0001, TERM_COUNT, null ) ) "TERM 603"
,MAX( DECODE( TERM, 0002, TERM_COUNT, null ) ) "TERM 703"
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-15 : 01:32:13

I have a very strange problem with the addition.

 SELECT 
 PERSON_ID
,NAB_COUNT
,MAX( DECODE( TERM, 0603, TERM_COUNT, null ) ) +  MAX( DECODE( TERM, 0703, TERM_COUNT, null ) ) "TERM_CNT"

,MAX( DECODE( TERM, 0603, TERM_COUNT, null ) ) "TERM_0603_CNT"
,MAX( DECODE( TERM, 0703, TERM_COUNT, null ) ) "TERM_0703_CNT"

GIVES ME

PERSON_ID  NAB_CNT  TERM_CNT      TERM 603   TERM 703
100009	     6	       3	    2	      1
100045	     10	       NULL	    2	      NULL

Why is it not counting the 2 in TERM_CNT from TERM_603??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-15 : 01:51:13

TERM 703 is NULL. Any computation that contain NULL value will result in NULL

so 2 + NULL will give you NULL

use COALESCE([column], 0) to check for NULL value and return 0


  COALESCE ( MAX( DECODE( TERM, 0603, TERM_COUNT, null ) ), 0 ) 
+ COALESCE ( MAX( DECODE( TERM, 0703, TERM_COUNT, null ) ), 0 ) "TERM_CNT"

refer http://www.techonthenet.com/oracle/functions/coalesce.php

KH
[spoiler]Time is always against us[/spoiler]

Chloe_19
Starting Member

44 Posts

Posted - 2012-03-15 : 01:57:43

Can't thank you enough.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-15 : 02:00:17

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources