Count funtion - 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

Count funtion

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-10 : 15:18:03

How would I write this?

count all BICNUM that are 002 and 202 from table iclaims

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-11-10 : 15:29:28

select count(bicnum)
from iclaims
where
bicnum between 002 and 202

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-10 : 15:36:16

Thanks didn't know that I could use Between

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-10 : 15:44:32

SELECT COUNT(*)
FROM iClaims
WHERE bicNum IN ('002', '202')

E 12°55'05.63"
N 56°04'39.26"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-12 : 07:51:19

Thanks Peso!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-12 : 08:02:26

Back again... I added onto my stored procedure:

SELECT COUNT(*) AS ALLRIB
FROM iClaims
WHERE (BICNUM IN ('002', '202'))

SELECT COUNT(*) AS RIBCount
FROM iClaims
WHERE (BICNUM IN ('002', '202')) AND (INET_IND <> 'n')

What to add to this stored procedure to get the % of the total of ALLRIB and RIBCount. I know I need to divide RIBCount by ALLRIB but don't know how to add that here.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-12 : 08:12:30

SELECT
(
COUNT(*)
/
(
SELECT COUNT(*) AS ALLRIB
FROM iClaims
WHERE BICNUM IN ('002', '202')
)
)
*100.00
FROM iClaims
WHERE (BICNUM IN ('002', '202')) AND (INET_IND <> 'n')

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-12 : 08:16:05

Or,
SELECT
(
sum(case when INET_IND<>'n' then 1 else 0 end)
/
sum(1)
)* 100.00
AS ALLRIB
FROM iClaims
WHERE BICNUM IN ('002', '202')

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-12 : 08:16:29

I tried that but it's coming out as Expr1 = 0.00

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-12 : 08:17:10

correct the above to AS ALLRIBPercent

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-12 : 08:17:29

The second statement is coming out 0.00 as well.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-12 : 08:18:29

And this ?

SELECT
(
sum(case when INET_IND<>'n' then 1.00 else 0.00 end)
/
sum(1.00)
)* 100.00
AS ALLRIB
FROM iClaims
WHERE BICNUM IN ('002', '202')

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-12 : 08:20:52

Thanks the second one did it:

SELECT
(
sum(case when INET_IND<>'n' then 1.00 else 0.00 end)
/
sum(1.00)
)* 100.00
AS ALLRIB
FROM iClaims
WHERE BICNUM IN ('002', '202')

Can you explain this procedure to me so I will know how to do this in the future. Where am I bringing in the ALLRibCount?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-12 : 08:29:48

SELECT
(
sum(case when INET_IND<>'n' then 1.00 else 0.00 end)/*(this gets you RIBCount, if INET_IND<>'n', you count it or else not*/
/
sum(1.00))/*(this gets you Allrib, you count everything here)*/
)* 100.00
AS percentage
FROM iClaims
WHERE BICNUM IN ('002', '202')

Hope its clear.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-12 : 08:46:18

[code]SELECT SUM(CASE WHEN INET_IND <> 'n' THEN 100.00 ELSE 0.00 END) / SUM(1.00) AS ALLRIB
FROM iClaims
WHERE BICNUM IN ('002', '202')[/code]

E 12°55'05.63"
N 56°04'39.26"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-11-12 : 15:36:02

Thanks so much Sakets and Peso for your answers and comments. I understand now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources