Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: SQL count() help please?

    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. #2
    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. #3
    Join Date
    Jul 2003
    Posts
    2,296
    Keep in mind that with analytics you can manipulate functions without the
    restrictions of grouping (not to mention additional advantages).
    PHP Code:
    select distinct
        pupilno
    surnamefirstnametotal_taken as 'TOTAL_GCSEs_TAKEN'
    from (
        
    select 
            pg
    .pupilno,
            
    p.surname,
            
    p.firstname,
            
    count(pg.gcsenoover (partition by pg.pupilnototal_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
    Last edited by The_Duck; 01-04-06 at 12:00.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •