Professional Web Applications Themes

aggregate functions -- does value exist in group - MySQL

Hi guys, I'm trying to use aggregate functions in a big way for the first time, and I've run into difficulty solving what must be a common problem. I'm counting the rows in groups, which is fine, something like this (simplified): SELECT sessions.time, sessions.location, COUNT( bookings.student_id ) AS takeup FROM sessions LEFT JOIN bookings ON sessions.id=bookings.session_id GROUP BY sessions.id; This works as expected, I get the number of students booked into a session no problem. However, I would like, in the same query, to discover whether a given student is booked into the session. I've been using SELECT sessions.time, sessions.location, ...

  1. #1

    Default aggregate functions -- does value exist in group

    Hi guys,
    I'm trying to use aggregate functions in a big way for the first time,
    and I've run into difficulty solving what must be a common problem.
    I'm counting the rows in groups, which is fine, something like this
    (simplified):

    SELECT sessions.time, sessions.location, COUNT( bookings.student_id )
    AS takeup FROM sessions LEFT JOIN bookings ON
    sessions.id=bookings.session_id GROUP BY sessions.id;

    This works as expected, I get the number of students booked into a
    session no problem.
    However, I would like, in the same query, to discover whether a given
    student is booked into the session. I've been using

    SELECT sessions.time, sessions.location, COUNT( bookings.student_id )
    AS takeup, IF( bookings.student_id='some_id', 1, 0 ) AS booked FROM
    sessions LEFT JOIN bookings ON sessions.id=bookings.session_id GROUP BY
    sessions.id;

    which I thought was working, but I've discovered is only checking the
    first row in the group.

    Is there a way of checking through the whole group and finding if a
    value is present in it? I guess a HAVING clause would produce that
    behaviour, but that would upset my COUNT( ) column? Will I have to
    resort to multiple queries?

    Thanks,
    Jez.

    jezaustin@gmail.com Guest

  2. #2

    Default Re: aggregate functions -- does value exist in group

    [email]jezaustin[/email] wrote:
    > Hi guys,
    > I'm trying to use aggregate functions in a big way for the first time,
    > and I've run into difficulty solving what must be a common problem.
    > I'm counting the rows in groups, which is fine, something like this
    > (simplified):
    >
    > SELECT sessions.time, sessions.location, COUNT( bookings.student_id )
    > AS takeup FROM sessions LEFT JOIN bookings ON
    > sessions.id=bookings.session_id GROUP BY sessions.id;
    >
    > This works as expected, I get the number of students booked into a
    > session no problem.
    > However, I would like, in the same query, to discover whether a given
    > student is booked into the session. I've been using
    >
    > SELECT sessions.time, sessions.location, COUNT( bookings.student_id )
    > AS takeup, IF( bookings.student_id='some_id', 1, 0 ) AS booked FROM
    > sessions LEFT JOIN bookings ON sessions.id=bookings.session_id GROUP BY
    > sessions.id;
    >
    > which I thought was working, but I've discovered is only checking the
    > first row in the group.
    >
    > Is there a way of checking through the whole group and finding if a
    > value is present in it? I guess a HAVING clause would produce that
    > behaviour, but that would upset my COUNT( ) column? Will I have to
    > resort to multiple queries?
    >
    > Thanks,
    > Jez.
    >
    Jez,

    Sometimes it's just better to use multiple queries.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: aggregate functions -- does value exist in group


    [email]jezaustin[/email] wrote:
    > Hi guys,
    > I'm trying to use aggregate functions in a big way for the first time,
    > and I've run into difficulty solving what must be a common problem.
    > I'm counting the rows in groups, which is fine, something like this
    > (simplified):
    >
    > SELECT sessions.time, sessions.location, COUNT( bookings.student_id )
    > AS takeup FROM sessions LEFT JOIN bookings ON
    > sessions.id=bookings.session_id GROUP BY sessions.id;
    >
    > This works as expected, I get the number of students booked into a
    > session no problem.
    > However, I would like, in the same query, to discover whether a given
    > student is booked into the session. I've been using
    >
    > SELECT sessions.time, sessions.location, COUNT( bookings.student_id )
    > AS takeup, IF( bookings.student_id='some_id', 1, 0 ) AS booked FROM
    > sessions LEFT JOIN bookings ON sessions.id=bookings.session_id GROUP BY
    > sessions.id;
    >
    > which I thought was working, but I've discovered is only checking the
    > first row in the group.
    >
    > Is there a way of checking through the whole group and finding if a
    > value is present in it? I guess a HAVING clause would produce that
    > behaviour, but that would upset my COUNT( ) column? Will I have to
    > resort to multiple queries?
    >
    > Thanks,
    > Jez.
    How strange that you should ask this just 2 days after I did a very
    similar thing. I was looking for the number of people booked on a
    course and also whether the particular user performing the query was
    booked on the course.

    Take a look at the first posting in the thread Conditional INSERT which
    I posted on Mon, Aug 21 2006 at 6:01 pm.
    The column "mybookings.course_id booked" is set to the course ID if the
    particular student is booked on the course, otherwise it is null.

    Captain Paralytic Guest

  4. #4

    Default Re: aggregate functions -- does value exist in group


    Captain Paralytic wrote:
    > Take a look at the first posting in the thread Conditional INSERT which
    > I posted on Mon, Aug 21 2006 at 6:01 pm.
    > The column "mybookings.course_id booked" is set to the course ID if the
    > particular student is booked on the course, otherwise it is null.
    Thankyou so much! That thread is totally relevant to what I'm doing
    too, as I'm sure you surmised.

    To summarise for anyone too lazy to find the Captain's thread: the
    trick is to Join the bookings table twice, so that COUNT() can look at
    bookings1, and IF() can look at bookings2...

    SELECT IF( bookings2.student_id, 1, 0 ), COUNT( bookings1.session_id )
    FROM sessions
    LEFT JOIN bookings AS bookings1 ON sessions.id=bookings1.session_id
    LEFT JOIN bookings AS bookings2 ON sessions.id=bookings2.session_id
    AND bookings2.student_id=2 GROUP BY sessions.id;

    This isn't something I'd have thought of by myself...

    jezaustin@gmail.com Guest

Similar Threads

  1. three table outer join with aggregate functions
    By jhilty in forum Coldfusion Database Access
    Replies: 2
    Last Post: July 13th, 09:48 AM
  2. WARNING: group with ID NNN does not exist
    By Ed L. in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 29th, 10:36 PM
  3. #25049 [NEW]: mysql functions don't exist
    By jmichae3 at yahoo dot com in forum PHP Development
    Replies: 0
    Last Post: August 12th, 08:41 AM
  4. Replies: 3
    Last Post: July 4th, 01:53 PM
  5. Replies: 2
    Last Post: June 30th, 09:48 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139