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.
| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2004-03-06 : 12:43:28
|
Hi,This query returns the number of items in each group...select count(*) from table1 Group by fk How do I get the average number of items in each group?select avg(count(*)) from table1 Group by pk gives...'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.'Cheers,X-Factor. |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-03-06 : 23:56:53
|
| You could wrap the first expression in a derived table:SELECT AVG(CountOfBlah) FROM ( SELECT COUNT(*) AS CountOfBlah FROM table1 GROUP BY col1) aNote that a derived table must have an alias.OS |
 |
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-08 : 11:58:33
|
| Average of what? Are you asking for the average number overall the fk(makes sense), or average within the fk (doesnt make sense with information provided)For example, if your table has the following fields and valuesfk f1 f2 ....fx1 1 1 ....1 3 1 ....1 2 1 ....1 5 2 ....2 3 1 ....2 4 1 ....Then fk, count(*) from table group by fk would return1 42 2There is no way to get an average number of fk= 1 - what would you be averaging? If you want to know the average number of records overall the fk fields then you can select count(*)/count(distinct fk) from tablewhich would return 3If however, one of your other fields contains a count of the number of whatever for each fk, then you can average that fieldselect fk,count(*), avg(f2) from table group by fkIt all depends on what you're trying to acomplish, but your question as worded doesnt make sense. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2004-03-08 : 13:16:15
|
| It made enough sense to mohdowais for them to give me a solution that worked.Though I did make an error... 'Group by pk' should have been 'Group by fk' in the second statement. Sorry if that confused matters.But yeah, select count(*)/count(distinct fk) is a much simpler way of achieving the same result. I was just playing around with grouping. |
|
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-08 : 14:02:47
|
| Thats okay x-factor, I was assuming that was what you were asking, which is why I posted the code, but the math major in me made me question the wording of your problem (and because ambiguity in specs is probably the most common reason why code doesnt do what the user wants it do to). |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-03-09 : 01:10:14
|
Yes, Chris, your solution was elegant. But the derived table solution is a good workaround for "aggregating aggregates" when inline calculations might not work.And yes, we are used to guesstimating requirements from ambiguous specs  OS |
|
|
|
shijobaby
Starting Member
44 Posts |
Posted - 2009-08-21 : 06:25:16
|
| The ways to avoid this error is simple just look into my posthttp://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html |
|
|
|
|
|
|
|
|
Avg of count