Combining 2 Count Statements Help (Thank you)

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

Combining 2 Count Statements Help (Thank you)

Author

Topic

Dani_87
Starting Member

14 Posts

Posted - 2012-03-01 : 20:47:25

Hi.

I am trying to join the 2 Count statements.
Any Help would be appreciated.

SELECT DISTINCT
L_id,
P_cde,MAX(counter) AS count_of_provider

FROM(SELECT L, P,count(*)counter

FROM TABLE1

WHERE G_status ='IN'

GROUP BY L.id, P_cde
ORDER BY 3)
GROUP BY L_id, P_cde
ORDER BY L_id ASC

and

Select distinct
L_ID,
T_M,MAX(counter) as COUNT_OF_TERM

FROM(select L_id, T_M,COUNT(*)counter
FROM TABLE 1

where G_status ='in'

GROUP BY L_id, T_M
order by 3)
GROUP BY L_ID, T_M
ORDER BY L_ID ASC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-01 : 20:52:08

How do you want it to combine ? how is the expected result looks like ?

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

Dani_87
Starting Member

14 Posts

Posted - 2012-03-01 : 21:26:50

L_ID P_CDE P_CNT T_M T_CNT

100000 MUR 2 603 2
100000 MIT 1 502 1
100001 UNA 1 309 2
100002 UNA 1 711 3
100003 CUN 2 502 7
100005 GRF 1 309 2
100005 GRF 11 603 1

Matching the L_ID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-01 : 21:43:48

[code]
SELECT P.L_id, P_P_cde, P.P_CNT, T.T_M, T_CNT
FROM
(
SELECT L_id, P_cde, MAX(counter) as P_CNT
FROM
(
SELECT L_id, P_cde, count(*) counter
FROM TABLE1
WHERE G_status = 'IN'
GROUP BY L_id, P_cde
) P
GROUP BY L_id, P_cde
) P
INNER JOIN
(
SELECT L_id, T_M, MAX(counter) as T_CNT
FROM
(
SELECT L_id, T_M, count(*) counter
FROM TABLE1
WHERE G_status = 'IN'
GROUP BY L_id, T_M
) P
GROUP BY L_id, T_M
) T ON P.L_id = T.L_id
[/code]

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

Dani_87
Starting Member

14 Posts

Posted - 2012-03-01 : 22:05:40

Thank you very much khtan.

If I wanted to continue combining more counts with different variables I would use the same procedure, Correct?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-01 : 22:21:03

yes. you can

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

Dani_87
Starting Member

14 Posts

Posted - 2012-03-03 : 02:27:56

Hey Khtan.
Can I ask you another question?
This one is a little more difficult.

Dani_87
Starting Member

14 Posts

Posted - 2012-03-03 : 05:03:32

Hey Khtan.
Can I ask you another question?
This one is a little more difficult.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-03 : 05:14:48

sure, if it is not related to this, just post it as a new thread

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

Dani_87
Starting Member

14 Posts

Posted - 2012-03-03 : 05:29:10

I am trying to join these 2 frequency counts by ID (as last time)
Indicidualy they work.

So should look something like

ID Payment CNT Payment % MARK MARK_CNT MARK FAILS%

50 7 2 85% F 2 89%

--Payment

SELECT ab.Person_id
,ab.Payment_Style
,ab.Cnt
,CAST(ab.Cnt AS FLOAT) / CAST(SUM(ab.Cnt) OVER(PARTITION BY ab.oua_id) AS FLOAT)* 100 "% of Payment Style"

FROM(
SELECT a.Person_id
,a.Payment_Style
,COUNT(*) Cnt
FROM TABLE_6 A
WHERE a.Payment_Style in ('FEE-HELP','CASH')
GROUP BY a.Person_id,
a.Payment_Style
) ab
ORDER BY ab.Person_id

--MARK

SELECT ab.Person_id
,ab.MARK
,ab.Cnt

,CAST(ab.Cnt AS FLOAT) / CAST(SUM(ab.Cnt) OVER(PARTITION BY ab.oua_id) AS FLOAT)* 100 "% of MARK FAILS P"

FROM(
SELECT A.Person_id
,a.MARK
,COUNT(*) Cnt
FROM TABLE_6 A
WHERE a.MARK in ('W','NA','Z','N')
GROUP BY A.Person_id, a.MARK
) ab
ORDER BY ab.PERSON_id

Chloe_19
Starting Member

44 Posts

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

Hey sorry to bother you agin but I got an error I cant figure out.
I am trying to calculate the % of grades for HD

,ROUND (COALESCE (MAX( DECODE( OUA_GRADE, 'HD', OUA_GRADE_COUNT, NULL ) ), 0 ) * 100.0 / (COALESCE (MAX( DECODE( OUA_GRADE, 'HD', OUA_GRADE_COUNT, NULL ) ), 0 )
+ COALESCE (MAX( DECODE( OUA_GRADE, 'D', OUA_GRADE_COUNT, NULL ) ), 0 )+ COALESCE (MAX( DECODE( OUA_GRADE, 'CP', OUA_GRADE_COUNT, NULL ) ), 0 )
+ COALESCE (MAX( DECODE( OUA_GRADE, 'C', OUA_GRADE_COUNT, NULL ) ), 0 )+ COALESCE (MAX( DECODE( OUA_GRADE, 'P', OUA_GRADE_COUNT, NULL ) ), 0 ) 
+ COALESCE (MAX( DECODE( OUA_GRADE, 'EX', OUA_GRADE_COUNT, NULL ) ), 0 )+ COALESCE (MAX( DECODE( OUA_GRADE, 'CM', OUA_GRADE_COUNT, NULL ) ), 0 ) 
+ COALESCE (MAX( DECODE( OUA_GRADE, 'I', OUA_GRADE_COUNT, NULL ) ), 0 )+ COALESCE (MAX( DECODE( OUA_GRADE, 'N', OUA_GRADE_COUNT, NULL ) ), 0 )
+ COALESCE (MAX( DECODE( OUA_GRADE, 'NA', OUA_GRADE_COUNT, NULL ) ), 0 )+ COALESCE (MAX( DECODE( OUA_GRADE, 'NY', OUA_GRADE_COUNT, NULL ) ), 0 ) 
+ COALESCE (MAX( DECODE( OUA_GRADE, 'RP', OUA_GRADE_COUNT, NULL ) ), 0 ) + COALESCE (MAX( DECODE( OUA_GRADE, 'W', OUA_GRADE_COUNT, NULL ) ), 0 )
+ COALESCE (MAX( DECODE( OUA_GRADE, 'WRO', OUA_GRADE_COUNT, NULL ) ), 0 )  )) "HD %"

But i get an error: ORA-01476: divisor is equal to zero

I dono how this can be because i used the same method on payment method to get the % and worked fine as you can see below:

,COALESCE (MAX( DECODE( PAYMENT_MTHD, 'CASH', PAYMENT_MTHD_COUNT, NULL ) ), 0 ) "PAYMENT_CASH_CNT"
,COALESCE (MAX( DECODE( PAYMENT_MTHD, 'FEE-HELP', PAYMENT_MTHD_COUNT, NULL ) ), 0 ) "PAYMENT_FEE-HELP_CNT"
,COALESCE (MAX( DECODE( PAYMENT_MTHD, 'Credit Card', PAYMENT_MTHD_COUNT, NULL ) ), 0 ) "PAYMENT_Credit_Card_CNT"
,COALESCE (MAX( DECODE( PAYMENT_MTHD, 'Cheque / Money Order', PAYMENT_MTHD_COUNT, null ) ), 0 ) "CHEQUE / MONEY ORDER_CNT"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-15 : 21:21:34

you have divide by 0 error.
example :
a / b

if the value of b is 0, you will get that error.

you could use NULLIF to check if the value is 0, return as NULL. So you will be divide by NULL and of-course get NULL as result

a / NULLIF ( b, 0)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources