Cannot perform an aggregate function on an express

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

SQL Server 2000 Forums

SQL Server Development (2000)

Cannot perform an aggregate function on an express

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2002-12-12 : 17:43:38

I am trying to run following query:

select ((count(policy_key))/(sum(count(policy_key)))) as '%pop'
from policy

but it keep showing error:Cannot perform an aggregate function on an express..

Any information would be appreciated..

Thank you

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-12 : 18:00:01

You can't Count() a Sum(), or vice versa. You can only calculate one aggregate in a single expression:

SELECT Count(policy_key)/Sum(policy_key) AS "%pop" FROM policy

Also, if policy_key is any kind of integer, you will get zero as a percentage. Try this instead:

SELECT Cast(Count(policy_key) as real)/Cast(Sum(policy_key) as real) AS "%pop" FROM policy

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-12 : 18:01:04

quote:

select ((count(policy_key))/(sum(count(policy_key)))) as '%pop'
from policy

Could you state in words what you are trying to calculate? I can't see what sum(count(policy_key)) means...

sorry robvolk - the old 2 second thing...
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 12/12/2002 18:02:41

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-12 : 18:17:40

count returns 1 value if you don't specify a group clause so what are you trying to do count all the values and divide by the sum???
If so what you want is count()/sum().

Can you post your data and some expected results that way we can help you.

This is what happens when you are in the middle of answering a post and you have to visit the can.

Edited by - ValterBorges on 12/12/2002 18:18:59

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-12-12 : 18:23:48

quote:

This is what happens when you are in the middle of answering a post and you have to visit the can.

I think you were missing an "aah" in there somewhere...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jung1975
Aged Yak Warrior

503 Posts

Posted - 2002-12-12 : 18:47:05

Thank you for your quick response.

But, I need to divide count(policy_key) by Sum of count(policy_key) not just sum of policy.

Is there any alterntive way that I can calculate : Count(policy_key) / sum(count(policy_key)) ?

I would really appreciated any help you can give to me

Thnak you

jung1975
Aged Yak Warrior

503 Posts

Posted - 2002-12-12 : 19:08:43

Ok, I calculated ‘#ACCTS’ column using count (policy_key)
Result is:

#ACCTS
----------------
16
11
10
8
1920
:
:
:
-----------------
Sum: ####

, and Now I am trying calculate the %pop column using Sum of #ACCTS column

#ACCTS %pop
---------------- -----------
16 16 / sum(#ACCT)
11 11 / sum(#ACCT)
10 10 / sum(#ACCT)
8 8 / sum(#ACCT)
19 19 / sum(#ACCT)
:
:
:
-----------------
Sum: ####

Thank you

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-12 : 20:21:24

Still don't know what you want so here are a couple of possibilities.

CREATE TABLE #ACCTS
(
userid int,
policy_key int
)

INSERT INTO #ACCTS (userid, policy_key) VALUES (1,1)
INSERT INTO #ACCTS (userid, policy_key) VALUES (2,1)
INSERT INTO #ACCTS (userid, policy_key) VALUES (3,2)
INSERT INTO #ACCTS (userid, policy_key) VALUES (4,1)
INSERT INTO #ACCTS (userid, policy_key) VALUES (5,3)
INSERT INTO #ACCTS (userid, policy_key) VALUES (6,4)
INSERT INTO #ACCTS (userid, policy_key) VALUES (7,4)
INSERT INTO #ACCTS (userid, policy_key) VALUES (8,4)
INSERT INTO #ACCTS (userid, policy_key) VALUES (9,4)

select count(userid) NumACCTS, CAST(count(userid) As Real)/(select sum(userid) from #ACCTS) As percentagepop
from #ACCTS
GROUP BY policy_key

DROP TABLE #ACCTS

OR

CREATE TABLE #ACCTS
(
groupid int,
policy_key int
)

INSERT INTO #ACCTS (groupid, policy_key) VALUES (1,1)
INSERT INTO #ACCTS (groupid, policy_key) VALUES (1,2)
INSERT INTO #ACCTS (groupid, policy_key) VALUES (1,3)
INSERT INTO #ACCTS (groupid, policy_key) VALUES (4,1)
INSERT INTO #ACCTS (groupid, policy_key) VALUES (5,3)
INSERT INTO #ACCTS (groupid, policy_key) VALUES (5,4)
INSERT INTO #ACCTS (groupid, policy_key) VALUES (8,4)
INSERT INTO #ACCTS (groupid, policy_key) VALUES (8,4)
INSERT INTO #ACCTS (groupid, policy_key) VALUES (9,4)

SELECT A.groupid, A.CountACCTS, Cast(A.CountACCTS As Real)/B.SumACCTS As Percentage
FROM
(
select groupid, COUNT(policy_key) CountACCTS
from #ACCTS
GROUP BY groupid
) A
INNER JOIN
(
select groupid, SUM(policy_key) SumACCTS
from #ACCTS
GROUP BY groupid
) B ON A.groupid = B.groupid

DROP TABLE #ACCTS

Edited by - ValterBorges on 12/12/2002 20:40:43

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-13 : 07:31:23

I think I figured out what you need (the ####'s in your example didn't help much, I must tell you though).

SELECT
policy_key,
COUNT(*) as CountOfKey,
CONVERT(decimal(1,4),COUNT(*) / (SELECT Count(*) FROM PolicyKey)) as [%policy]
FROM
policy
GROUP BY
Policy_Key

If you want these results:

Policy_key
A
A
A
B
B

count of A: 3
count of B: 2

% of A: 3/5 = 60%
% of B: 2/5 = 40%

???

(the word "policy" looks really weird when you keep typing it over and over.....)

- Jeff

Edited by - jsmith8858 on 12/13/2002 08:01:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources