How to find average of another nested count? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to find average of another nested count?

Author

Topic

ngquochung
Starting Member

13 Posts

Posted - 2006-11-26 : 01:47:16

How to find the average of another nested count aggregation?
For example
select count (PANUM)
from PAPER
where AVG (count(PANUM))>5
Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-26 : 03:33:47

Your question doesn't make sense what-so-ever.
Firstly you are taking COUNT and then you are taking AVG of that COUNT. What are you trying to achive?

Harsh Athalye
India.
"Nothing is Impossible"

ngquochung
Starting Member

13 Posts

Posted - 2006-11-26 : 04:13:30

In the department, there are many staff and every staff authors a certain number of paper (panum is the paper id).
What I want to achieve is: display the author that has quantity of paper (for each author only) greater than the average of paper quantity (for all staffs member in the department)

--DEPTNUM is the ID represent the DEPARTMENT
--ACNUM is the ID represent the AUTHOR

select ACNUM, FAMNAME, GIVENAME
from AUTHOR, ACADEMIC
where AUTHOR.ACNUM = ACADEMIC.ACNUM
and ACNUM in (select ACNUM
from AUTHOR
group by ACNUM
having count (ACNUM)> (select avg (count(ACNUM))
from ACADEMIC
group by DEPTNUM))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-26 : 04:23:18

You mean to select to all authors whos papers quantity is larger than the average quantity of all authors?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-26 : 04:48:50

Where in this case, the PANUM comes into the picture. It would be helpful if you could post your table structures here.

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-26 : 05:02:36

May be this:

Select ACNUM, FAMNAME, GIVENAME
from 
	AUTHOR JOIN ACADEMIC
ON 
	AUTHOR.ACNUM = ACADEMIC.ACNUM 
WHERE
	AUTHOR.ACNUM in 
	(
	select ACNUM
	from AUTHOR au
	group by ACNUM
	having count(ACNUM) > 
					(
					select count(ACNUM)/(SELECT COUNT(ACNUM)*1.0 FROM ACADEMIC)
					from ACADEMIC ac
					WHERE ac.DEPTNUM = au.DEPTNUM
					)
	)

Harsh Athalye
India.
"Nothing is Impossible"

ngquochung
Starting Member

13 Posts

Posted - 2006-11-26 : 05:27:46

No I mean
the number of paper by each author GREATER THAN the average of (the number of paper by all the author of the department)

ngquochung
Starting Member

13 Posts

Posted - 2006-11-26 : 05:28:05

Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(PaNum, AcNum)
Field(FieldNum, ID, Title)
Interest(FieldNum, AcNum, Descrip)

ngquochung
Starting Member

13 Posts

Posted - 2006-11-26 : 05:32:58

Hi, I'm afraid ur solution is not correct. It results in such errors:
Server: Msg 8118, Level 16, State 1, Line 1
Column 'AUTHOR.ACNUM' is invalid in the select list because it is not contained in an aggregates function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'ACADEMIC.FAMNAME' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'ACADEMIC.GIVENAME' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-26 : 06:03:48

select a.panum, a.acnum, p.title
from (
select panum
from paper
group by panum
having count(*) >= (SELECT 1.0 * COUNT(*) / COUNT(DISTINCT PANUM) FROM paper
) q
inner join paper p on p.panum = q.panum
inner join author a on a.panum = q.panum

Peter Larsson
Helsingborg, Sweden

ngquochung
Starting Member

13 Posts

Posted - 2006-11-27 : 09:15:23

Hi everyone,
Thank you a lot for your assistance. I still need some more help.

The full question is: Considering the Griffith University, which staff have authored more than the average number of papers authored by a staff member in the department that they are from? Return the AcNum and name of staff, and the number of papers they have written, in alphabetical order of their family name. Note that a paper may have more than one author and it should be counted for each author. Staff who do not write papers at all are not counted.

My solution is:
select AUTHOR.ACNUM, FAMNAME, GIVENAME, count (PANUM)
from DEPARTMENT, ACADEMIC, AUTHOR
where DEPARTMENT.DEPTNUM = ACADEMIC.DEPTNUM
and ACADEMIC.ACNUM = AUTHOR.ACNUM
and INSTNAME = 'Griffith University'
and AUTHOR.ACNUM in
(
select ACNUM
from AUTHOR
group by ACNUM
having count (PANUM)>any
(
select count(PANUM)
/count(distinct ACNUM)
from AUTHOR
group by ACNUM
)
)
group by AUTHOR.ACNUM, FAMNAME, GIVENAME
order by FAMNAME

I think there is still mistakes due to the incorrect grouping. I don't know how to fix it. When testing, it results in missing some value in the attribute panum.

The data structure of tables is already provided in last post.
Thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 09:17:52

Start a new thread by send a new post or topic instead!

Peter Larsson
Helsingborg, Sweden

ngquochung
Starting Member

13 Posts

Posted - 2006-11-27 : 09:26:58

This is not new topic. This is virtually the full question from the brief questions in the previous posts also in this thread.
Regards,
NQH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 09:33:30

Are you able to present some sample data for each and one table?

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-27 : 09:59:22

please read the hint link in my sig and post what it asks for...you are not being very clear...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ngquochung
Starting Member

13 Posts

Posted - 2006-11-27 : 18:35:35

[code]PANUM ACNUM FAMNAME GIVENAME DEPTNUM
----------- ----------- -------------------- -------------------- ---
1 100 Kornreich Jeroen 100
1 101 Yee Mark 100
2 102 Fernandes Russell 100
3 103 Cohen Richard 100
3 104 Polemitis Joe 100
4 105 Brower Tony 100
5 106 Royce Mel 100
5 107 Christine Leonardo 100
5 108 Brandon Chuck 100
5 109 Gustafsson Hakan 101
5 111 Barton Michel 132
6 113 Sadhal Sharanya 101
7 114 Morley Chris 101
7 115 Garton Graham 145
7 116 Nair Marc 147
7 117 Baliga Lance 101
8 118 Rickert Hannu 145
9 119 Lamkin Bas 102
9 120 Tang Greg 102
10 121 Skelton Robert 102
11 122 Harris Mark 142
11 123 Wyld Mitchell 102
11 124 Haenssgen Francis 102
11 125 Ahlberg Grosek 102
11 126 Bodoh Anne 102[/code]

ngquochung
Starting Member

13 Posts

Posted - 2006-11-27 : 18:38:07

select PANUM, ACADEMIC.ACNUM, FAMNAME, GIVENAME, DEPTNUM

from AUTHOR join ACADEMIC on ACADEMIC.ACNUM = AUTHOR.ACNUM
where PANUM in (
                 select PANUM
                 from AUTHOR       
                 group by PANUM             
                 having count (ACNUM) <= all(
                                             select count (DEPTNUM)
                                             from ACADEMIC
                                             group by ACNUM
                                             )
               )
order by PANUM

Here is the solution of mine, it's not correct yet
Some tuples are missed.

PANUM       ACNUM       FAMNAME              GIVENAME             DEPTNUM     
----------- ----------- -------------------- -------------------- ----------- 
2           102         Fernandes            Russell              100
4           105         Brower               Tony                 100
6           113         Sadhal               Sharanya             101
8           118         Rickert              Hannu                145
10          121         Skelton              Robert               102
15          134         Owen                 Kenneth              103
19          142         Lawitzke             Ky                   105
20          143         Stewart              Andrew               106
21          144         Kinsella             Declan               106
23          148         Delavega             Demetrios            108
30          163         Peterson             Stefan               111
31          164         Stanton              Ballintijn           111

Regards,
NQH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources