SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to find average of another nested count?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ngquochung
Starting Member

13 Posts

Posted - 11/26/2006 :  01:47:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 11/26/2006 :  03:33:47  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

ngquochung
Starting Member

13 Posts

Posted - 11/26/2006 :  04:13:30  Show Profile  Reply with Quote
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))

Edited by - ngquochung on 11/26/2006 04:14:39
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/26/2006 :  04:23:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You mean to select to all authors whos papers quantity is larger than the average quantity of all authors?



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 11/26/2006 :  04:48:50  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 11/26/2006 :  05:02:36  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"

Edited by - harsh_athalye on 11/26/2006 05:05:10
Go to Top of Page

ngquochung
Starting Member

13 Posts

Posted - 11/26/2006 :  05:27:46  Show Profile  Reply with Quote
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)

Go to Top of Page

ngquochung
Starting Member

13 Posts

Posted - 11/26/2006 :  05:28:05  Show Profile  Reply with Quote
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)
Go to Top of Page

ngquochung
Starting Member

13 Posts

Posted - 11/26/2006 :  05:32:58  Show Profile  Reply with Quote
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.

Edited by - ngquochung on 11/26/2006 05:34:39
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/26/2006 :  06:03:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

ngquochung
Starting Member

13 Posts

Posted - 11/27/2006 :  09:15:23  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/27/2006 :  09:17:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Start a new thread by send a new post or topic instead!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ngquochung
Starting Member

13 Posts

Posted - 11/27/2006 :  09:26:58  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/27/2006 :  09:33:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you able to present some sample data for each and one table?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/27/2006 :  09:59:22  Show Profile  Reply with Quote
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



Go to Top of Page

ngquochung
Starting Member

13 Posts

Posted - 11/27/2006 :  18:35:35  Show Profile  Reply with Quote
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

Edited by - ngquochung on 11/27/2006 18:36:25
Go to Top of Page

ngquochung
Starting Member

13 Posts

Posted - 11/27/2006 :  18:38:07  Show Profile  Reply with Quote
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

Edited by - ngquochung on 11/27/2006 18:39:37
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000