| Author |
 Topic  |
|
|
ngquochung
Starting Member
13 Posts |
 Posted - 11/26/2006 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 11/26/2006 : 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 - 11/26/2006 : 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)) |
Edited by - ngquochung on 11/26/2006 04:14:39 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/26/2006 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 11/26/2006 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 11/26/2006 : 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" |
Edited by - harsh_athalye on 11/26/2006 05:05:10 |
|
|
|
ngquochung
Starting Member
13 Posts |
Posted - 11/26/2006 : 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 - 11/26/2006 : 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 - 11/26/2006 : 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. |
Edited by - ngquochung on 11/26/2006 05:34:39 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/26/2006 : 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 - 11/27/2006 : 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
Sweden
29138 Posts |
Posted - 11/27/2006 : 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 - 11/27/2006 : 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
Sweden
29138 Posts |
Posted - 11/27/2006 : 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 |
|
|
ngquochung
Starting Member
13 Posts |
Posted - 11/27/2006 : 18:35:35
|
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 |
|
|
|
ngquochung
Starting Member
13 Posts |
Posted - 11/27/2006 : 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 |
Edited by - ngquochung on 11/27/2006 18:39:37 |
|
|
| |
Topic |
|
How to find average of another nested count?
