1. Registered User
Join Date
Jan 2006
Posts
2

I hope someone can help me, I'm so tired and frustrated trying to work out how to do this one.

What I'm trying to do is list the total number of GCSE exams that a female pupil in year group 11 takes where the total number of GCSEs being taken is over 8.

The COUNT(PUPILGCSE.GCSENO) here gives me a row count for the number of girls 'G' in year group 11 'YR11'. But how do I narrow it down by the number of GCSE exams taken?

What I really want to say here in plain English is at the end of the WHERE clause is:
AND COUNT(PUPILGCSE.GCSENO) > 8. (where the row count is greater than 8). Any ideas how to do it?

SELECT PUPILGCSE.PUPILNO, PUPIL.SURNAME, PUPIL.FIRSTNAME, COUNT(PUPILGCSE.GCSENO) AS 'TOTAL_GCSEs_TAKEN'
FROM PUPILGCSE, PUPIL, TUTORGROUP
WHERE PUPILGCSE.PUPILNO = PUPIL.PUPILNO AND
PUPIL.TUTORGROUP = TUTORGROUP.TITLE AND TUTORGROUP.YEARGROUP = 'YR11' AND PUPIL.GENDER = 'G'
GROUP BY PUPILGCSE.PUPILNO;

Help is very much appreciated as it's taken me a full day to try and figure it out and Im still no closer.

Thanks a lot
Ross

2. Registered User
Join Date
Jan 2006
Posts
2

## Figured it!

I figured it, took me best part of a day but I did it. I needed HAVING.. I'll post this might help someone at some point.

SELECT PUPILGCSE.PUPILNO, PUPIL.SURNAME, PUPIL.FIRSTNAME, COUNT(PUPILGCSE.GCSENO) AS 'TOTAL_GCSEs_TAKEN'
FROM PUPILGCSE, PUPIL, TUTORGROUP
WHERE PUPILGCSE.PUPILNO = PUPIL.PUPILNO AND
PUPIL.TUTORGROUP = TUTORGROUP.TITLE AND TUTORGROUP.YEARGROUP = 'YR11' AND
PUPIL.GENDER = 'G'
GROUP BY PUPILGCSE.PUPILNO
HAVING COUNT(*) > 8;

3. Registered User
Join Date
Jul 2003
Posts
2,296
Keep in mind that with analytics you can manipulate functions without the
``` select distinct     pupilno, surname, firstname, total_taken as 'TOTAL_GCSEs_TAKEN' from (     select          pg.pupilno,         p.surname,         p.firstname,         count(pg.gcseno) over (partition by pg.pupilno) total_taken     from          pupilgcse pg,          pupil p,         tutorgroup t     where          pg.pupilno = p.pupilno and         p.tutorgroup = t.title and          t.yeargroup = 'YR11' and         p.gender = 'G' ) where total_taken > 8;  ```