Professional Web Applications Themes

Count GROUP BY query dilemma - MySQL

I need help creating a list of items with a corresponding count. I have 3 tables: event staff status I need to list the statuses and count the number of events for each based on a userid's existence in either "event" or "staff". For example, suppose the structure and data for each table looks like the following: table: event eventid userid statusid 1 1 2 table: staff eventid userid roleid 1 2 1 1 2 2 table: status statusid statusname 1 unscheduled 2 scheduled 3 rescheduled 4 cancelled For userid 1 (existing in table "event") the output would look like ...

  1. #1

    Default Count GROUP BY query dilemma

    I need help creating a list of items with a corresponding count.

    I have 3 tables:

    event
    staff
    status

    I need to list the statuses and count the number of events for each based on
    a userid's existence in either "event" or "staff".

    For example, suppose the structure and data for each table looks like the
    following:

    table: event
    eventid userid statusid

    1 1 2

    table: staff
    eventid userid roleid

    1 2 1
    1 2 2

    table: status
    statusid statusname

    1 unscheduled
    2 scheduled
    3 rescheduled
    4 cancelled

    For userid 1 (existing in table "event") the output would look like this:

    status event count

    unscheduled 0
    scheduled 1
    rescheduled 0
    cancelled 0

    For userid 2 (existing in table "staff") the output would be the same
    because eventid 1's statusid is 2 ("scheduled") even though userid 2 appears
    in the staff table twice.

    The following query will generate a list of statues and counts for ALL
    users:

    SELECT s.statusname, Count(e.statusid) AS statuscount
    FROM status AS s
    LEFT JOIN event e ON e.statusid = s.statusid
    GROUP BY s.statusid
    ORDER BY s.statusid

    Bottom line, I need a list of statuses with a corresponding count of events
    per status driven by a userid, whether the userid exists in table "event" or
    "staff". The same userid will never appear in both tables. The userid's in
    "event" are managers and the userid's in "staff" are worker bees.

    Help is greatly appreciated. Thanks!


    Bosconian Guest

  2. #2

    Default Re: Count GROUP BY query dilemma

    "Bosconian" <bosconianplanetx.com> wrote in message
    news:a92dnRRdqaXQeTfeRVn-qAcomcast.com...
    > Bottom line, I need a list of statuses with a corresponding count of
    > events
    > per status driven by a userid, whether the userid exists in table "event"
    > or
    > "staff". The same userid will never appear in both tables. The userid's in
    > "event" are managers and the userid's in "staff" are worker bees.
    It's not clear what you're tracking here. I think you need to redesign the
    schema to account for the one-to-many relationships. For example, the Staff
    table should have another table e.g. Staff_Role to record the multiple roles
    each staff person may have. Then the Staff table would have only one row
    per staff person.

    Also, I'm not sure about the Event table. I suspect you need to split this
    table into multiple tables too. Is it keyed by eventid or userid? Do the
    statuses apply to the event (and are therefore redundant on multiple rows)
    or to the user? In other words, what is it that's being scheduled?

    Finally, I wouldn't recommend designing a schema that put the people in
    different tables. That's a sure sign that a restructuring is called for.
    I'd do something like this:

    - Table Event records the event itself.
    - Table Person records all people, event attendees and staff.
    - Table Status just holds the four possible statuses for reference.
    - Table Attend records which people attend which events, and whether they
    attend as a user or as staff.
    - The Role table lists possible roles.
    - The Staff_Role table references both Role and Person; you may have
    multiple roles per staff member, so put multiple rows in the Staff_Role
    table.

    Then if the status applies to the whole event, you put a reference field in
    the Event table to the Status table. If the status applies to each
    individual attendee, you put the reference field in the Attend table.
    Either way, it becomes much easier to count the number of people per event
    and per status, by joining the Event and Attend tables.

    Once you have the correct schema to model the relationships, the query to
    generate a report is usually much easier to see.

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: Count GROUP BY query dilemma

    "Bill Karwin" <billkarwin.com> wrote in message
    news:dof484012g4enews4.newsguy.com...
    > "Bosconian" <bosconianplanetx.com> wrote in message
    > news:a92dnRRdqaXQeTfeRVn-qAcomcast.com...
    > > Bottom line, I need a list of statuses with a corresponding count of
    > > events
    > > per status driven by a userid, whether the userid exists in table
    "event"
    > > or
    > > "staff". The same userid will never appear in both tables. The userid's
    in
    > > "event" are managers and the userid's in "staff" are worker bees.
    >
    > It's not clear what you're tracking here. I think you need to redesign
    the
    > schema to account for the one-to-many relationships. For example, the
    Staff
    > table should have another table e.g. Staff_Role to record the multiple
    roles
    > each staff person may have. Then the Staff table would have only one row
    > per staff person.
    >
    > Also, I'm not sure about the Event table. I suspect you need to split
    this
    > table into multiple tables too. Is it keyed by eventid or userid? Do the
    > statuses apply to the event (and are therefore redundant on multiple rows)
    > or to the user? In other words, what is it that's being scheduled?
    >
    > Finally, I wouldn't recommend designing a schema that put the people in
    > different tables. That's a sure sign that a restructuring is called for.
    > I'd do something like this:
    >
    > - Table Event records the event itself.
    > - Table Person records all people, event attendees and staff.
    > - Table Status just holds the four possible statuses for reference.
    > - Table Attend records which people attend which events, and whether they
    > attend as a user or as staff.
    > - The Role table lists possible roles.
    > - The Staff_Role table references both Role and Person; you may have
    > multiple roles per staff member, so put multiple rows in the Staff_Role
    > table.
    >
    > Then if the status applies to the whole event, you put a reference field
    in
    > the Event table to the Status table. If the status applies to each
    > individual attendee, you put the reference field in the Attend table.
    > Either way, it becomes much easier to count the number of people per event
    > and per status, by joining the Event and Attend tables.
    >
    > Once you have the correct schema to model the relationships, the query to
    > generate a report is usually much easier to see.
    >
    > Regards,
    > Bill K.
    >
    >
    Hi Bill,

    Thanks (again) for your reply.

    Please allow me to clarify. The schema in my original post included
    references to foreign keys in unspecified tables and was for example only.
    The actual project schema closely matches the structure you outlined. I was
    attempting (perhaps unsuccessfully) to illustrate my specific query question
    without muddling things. I apologize if this was not clear or obvious.

    I essentially have a table of events, each with an owner. Each event can
    have multiple participants. I simply need to generate a list of statuses and
    event counts based on a specific user--whether they be owners or
    participants. In my example the userid foreign keys in tables "event" and
    "staff" both point to the same table "user".



    Bosconian Guest

  4. #4

    Default Re: Count GROUP BY query dilemma

    "Bill Karwin" <billkarwin.com> wrote in message
    news:dof484012g4enews4.newsguy.com...
    > "Bosconian" <bosconianplanetx.com> wrote in message
    > news:a92dnRRdqaXQeTfeRVn-qAcomcast.com...
    > > Bottom line, I need a list of statuses with a corresponding count of
    > > events
    > > per status driven by a userid, whether the userid exists in table
    "event"
    > > or
    > > "staff". The same userid will never appear in both tables. The userid's
    in
    > > "event" are managers and the userid's in "staff" are worker bees.
    >
    > It's not clear what you're tracking here. I think you need to redesign
    the
    > schema to account for the one-to-many relationships. For example, the
    Staff
    > table should have another table e.g. Staff_Role to record the multiple
    roles
    > each staff person may have. Then the Staff table would have only one row
    > per staff person.
    >
    > Also, I'm not sure about the Event table. I suspect you need to split
    this
    > table into multiple tables too. Is it keyed by eventid or userid? Do the
    > statuses apply to the event (and are therefore redundant on multiple rows)
    > or to the user? In other words, what is it that's being scheduled?
    >
    > Finally, I wouldn't recommend designing a schema that put the people in
    > different tables. That's a sure sign that a restructuring is called for.
    > I'd do something like this:
    >
    > - Table Event records the event itself.
    > - Table Person records all people, event attendees and staff.
    > - Table Status just holds the four possible statuses for reference.
    > - Table Attend records which people attend which events, and whether they
    > attend as a user or as staff.
    > - The Role table lists possible roles.
    > - The Staff_Role table references both Role and Person; you may have
    > multiple roles per staff member, so put multiple rows in the Staff_Role
    > table.
    >
    > Then if the status applies to the whole event, you put a reference field
    in
    > the Event table to the Status table. If the status applies to each
    > individual attendee, you put the reference field in the Attend table.
    > Either way, it becomes much easier to count the number of people per event
    > and per status, by joining the Event and Attend tables.
    >
    > Once you have the correct schema to model the relationships, the query to
    > generate a report is usually much easier to see.
    >
    > Regards,
    > Bill K.
    >
    >
    I should add that the event table holds lots of additional information about
    an event besides the owner (user) ID. The event_staff table is suppose to
    serve as a bridge table between the event and user tables.


    Bosconian Guest

  5. #5

    Default Re: Count GROUP BY query dilemma

    "Bill Karwin" <billkarwin.com> wrote in message
    news:dof484012g4enews4.newsguy.com...
    > "Bosconian" <bosconianplanetx.com> wrote in message
    > news:a92dnRRdqaXQeTfeRVn-qAcomcast.com...
    > > Bottom line, I need a list of statuses with a corresponding count of
    > > events
    > > per status driven by a userid, whether the userid exists in table
    "event"
    > > or
    > > "staff". The same userid will never appear in both tables. The userid's
    in
    > > "event" are managers and the userid's in "staff" are worker bees.
    >
    > It's not clear what you're tracking here. I think you need to redesign
    the
    > schema to account for the one-to-many relationships. For example, the
    Staff
    > table should have another table e.g. Staff_Role to record the multiple
    roles
    > each staff person may have. Then the Staff table would have only one row
    > per staff person.
    >
    > Also, I'm not sure about the Event table. I suspect you need to split
    this
    > table into multiple tables too. Is it keyed by eventid or userid? Do the
    > statuses apply to the event (and are therefore redundant on multiple rows)
    > or to the user? In other words, what is it that's being scheduled?
    >
    > Finally, I wouldn't recommend designing a schema that put the people in
    > different tables. That's a sure sign that a restructuring is called for.
    > I'd do something like this:
    >
    > - Table Event records the event itself.
    > - Table Person records all people, event attendees and staff.
    > - Table Status just holds the four possible statuses for reference.
    > - Table Attend records which people attend which events, and whether they
    > attend as a user or as staff.
    > - The Role table lists possible roles.
    > - The Staff_Role table references both Role and Person; you may have
    > multiple roles per staff member, so put multiple rows in the Staff_Role
    > table.
    >
    > Then if the status applies to the whole event, you put a reference field
    in
    > the Event table to the Status table. If the status applies to each
    > individual attendee, you put the reference field in the Attend table.
    > Either way, it becomes much easier to count the number of people per event
    > and per status, by joining the Event and Attend tables.
    >
    > Once you have the correct schema to model the relationships, the query to
    > generate a report is usually much easier to see.
    >
    > Regards,
    > Bill K.
    >
    >
    Bill,

    I've been thinking about your suggestions and agree the schema could use
    some work. My dilemma is that much of the current schema was predefined and
    any changes cannot break this existing structure.

    The aforementioned "staff" or "event_staff" table was my attempt to add
    support for additional participants associated with an event. The event
    table userid (owner) is part of the pre-existing structure.

    I'll mull it over and any suggestions in the meantime would be gladly
    accepted.

    Thanks!




    Bosconian Guest

  6. #6

    Default Re: Count GROUP BY query dilemma

    "Bill Karwin" <billkarwin.com> wrote in message
    news:dof484012g4enews4.newsguy.com...
    > "Bosconian" <bosconianplanetx.com> wrote in message
    > news:a92dnRRdqaXQeTfeRVn-qAcomcast.com...
    > > Bottom line, I need a list of statuses with a corresponding count of
    > > events
    > > per status driven by a userid, whether the userid exists in table
    "event"
    > > or
    > > "staff". The same userid will never appear in both tables. The userid's
    in
    > > "event" are managers and the userid's in "staff" are worker bees.
    >
    > It's not clear what you're tracking here. I think you need to redesign
    the
    > schema to account for the one-to-many relationships. For example, the
    Staff
    > table should have another table e.g. Staff_Role to record the multiple
    roles
    > each staff person may have. Then the Staff table would have only one row
    > per staff person.
    >
    > Also, I'm not sure about the Event table. I suspect you need to split
    this
    > table into multiple tables too. Is it keyed by eventid or userid? Do the
    > statuses apply to the event (and are therefore redundant on multiple rows)
    > or to the user? In other words, what is it that's being scheduled?
    >
    > Finally, I wouldn't recommend designing a schema that put the people in
    > different tables. That's a sure sign that a restructuring is called for.
    > I'd do something like this:
    >
    > - Table Event records the event itself.
    > - Table Person records all people, event attendees and staff.
    > - Table Status just holds the four possible statuses for reference.
    > - Table Attend records which people attend which events, and whether they
    > attend as a user or as staff.
    > - The Role table lists possible roles.
    > - The Staff_Role table references both Role and Person; you may have
    > multiple roles per staff member, so put multiple rows in the Staff_Role
    > table.
    >
    > Then if the status applies to the whole event, you put a reference field
    in
    > the Event table to the Status table. If the status applies to each
    > individual attendee, you put the reference field in the Attend table.
    > Either way, it becomes much easier to count the number of people per event
    > and per status, by joining the Event and Attend tables.
    >
    > Once you have the correct schema to model the relationships, the query to
    > generate a report is usually much easier to see.
    >
    > Regards,
    > Bill K.
    >
    >
    I have decided to simply use 2 queries. The userid's in event and
    event_staff will never be the same so combining the results in 1 recordset
    is unnecessary and makes my life MUCH simplier. My program logic knows
    whether the user is a "manager" or "staff" so adding a condition is a
    breeze.

    Ok, I feel better now. :-)


    Bosconian Guest

Similar Threads

  1. Order by results of count using 'group by'
    By salvador in forum MySQL
    Replies: 3
    Last Post: August 2nd, 06:49 PM
  2. COUNT and GROUP
    By brianism in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 2nd, 09:09 PM
  3. (simple?) query dilemma--help please
    By Bosconian in forum MySQL
    Replies: 2
    Last Post: November 19th, 02:00 AM
  4. Need Asp recordset Group / Count like values
    By gotcha in forum ASP Database
    Replies: 2
    Last Post: July 20th, 05:12 PM
  5. Group by, count, type of query beyond my ability
    By Ray at in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 8th, 09:29 AM

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