Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Count(count(count()))

    Well that was just to get your attention. Here's the thing: I need to write a query which creates a recordset, of available lectures, with a field with the available spaces left in the lecture room.

    I want to list the lectures and say that the capacity is 'X' (label.qty)from table "A" then count the registrants from table "B" who have selected that class. Subtract the count from the capacity to display the available spaces.

    Alt. 1 if the count is >= capacity the lecture is not included in the recordset.



    Here is table "A":
    CREATE TABLE `label` (
    `id_lbl` int(3) unsigned NOT NULL auto_increment,
    `descr_lbl` varchar(80) default '',
    `group_lbl` tinyint(3) unsigned default NULL,
    `order_lbl` tinyint(3) unsigned default NULL,
    `cost` decimal(8,2) default NULL,
    `qty` int(50) default NULL, ----capacity of class
    PRIMARY KEY (`id_lbl`)
    ) ENGINE=MyISAM AUTO_INCREMENT=71 DEFAULT CHARSET=latin1


    Table "B" is a record of each registrant which has a field for classes holding the "id_lbl" of the class they have selected.


    Oldnickj

  2. #2
    Join Date
    Jan 2009
    Posts
    124

    Count PS

    I guess this is a sub-query thing but I can't quite visualize it.

    any ideas (r937)!

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj View Post
    any ideas (r937)!
    yes, certainly

    what's with the _lbl lable lables?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2009
    Posts
    124

    American spelling

    you may need an extra "u" being way North...

    We also spell "the" "teh" usually.

    Anyway.. so for each class record in the label table I need to count the instances of the label.id_lbl in the registration table then subtract that from the label.qty, return the sum and return nothing if a 0.

    oldnickj

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's make this a two-step solution

    teh first step is to write a simple GROUP BY query on the registration table by itself, to get a count for each lbl_id, oops i mean id_lbl, in that table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2009
    Posts
    124

    OK group by

    Select count(breChoice_reg)
    FROM Meet_reg
    group by breChoice_reg

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj View Post
    Select count(breChoice_reg)
    FROM Meet_reg
    group by breChoice_reg
    close, but it's off in a couple of ways

    first of all, by selecting only the count, you're going to get a result set that looks like this --
    Code:
    count(breChoice_reg)
    12
    42
    9
    37
    so just having the counts by themselves is not very useful, because you can't tell which label each count belongs to

    secondly, GROUP BY breChoice_reg would give you counts for each breChoice_reg, rather than for each class/lecture, which would be id_lbl instead

    (aside: why would you call a class or lecture a "label"? people's creativity never ceases to amaze me)

    so what you were looking for was this --
    Code:
    SELECT id_lbl
         , COUNT(*) AS registrations
      FROM Meet_reg
    GROUP
        BY id_lbl
    please confirm that this works and gives the correct results, before we go on to the second of our two steps
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2009
    Posts
    124

    Count() group by

    the label table is a look up table.

    SELECT breChoice_reg
    , COUNT(*) AS registrations
    FROM Meet_reg
    GROUP
    BY breChoice_reg

    result:
    62 3
    63 2
    65 1
    66 2

    this is the correct result. "breChoice" holds the id of the name of the lecture (they call them classes as in school classes but I decided not not use that word because I'm CSS sensitive!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the next step is simple -- just join to this subquery with a LEFT OUTER JOIN (outer instead of inner because you want to allow for lectures which have no registrations at all yet)
    Code:
    SELECT ...
      FROM label 
    LEFT OUTER
      JOIN ( SELECT breChoice_reg
                  , COUNT(*) AS registrations
               FROM Meet_reg
             GROUP
                 BY breChoice_reg ) AS c
        ON c.breChoice_reg = label.id_lbl
     WHERE label.qty >= COALESCE(c.registrations,0)
    i shall leave it to you to figure out what goes in the SELECT clause

    note that when a subquery is used like this, it's called a derived table, and you can think of the result set of the subquery as an actual table (which it is)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2009
    Posts
    124

    Colaesce!!!

    So spring a new one on me...it works and is so simple it's going to take a while to understand why it works!

    thanks

  11. #11
    Join Date
    Jan 2009
    Posts
    124

    Count 2 (twu)

    Works great in an editor but not on the web page. "standby" is not being echoed perhaps because it is producing NULL when I try to subtract registrations from qty and there are no reservations. see image!





    Here is my new query:

    SELECT descr_lbl, qty
    , registrations
    , qty-registrations AS standby
    FROM label
    LEFT OUTER
    JOIN ( SELECT breChoice_reg
    COUNT(*) AS registrations
    FROM Meet_reg
    GROUP BY breChoice_reg ) AS c
    ON c.breChoice_reg = label.id_lbl
    WHERE label.qty >= COALESCE(c.registrations,0)
    AND group_lbl = 7
    Attached Thumbnails Attached Thumbnails oldnickj.jpg  

  12. #12
    Join Date
    Jan 2009
    Posts
    124

    Count()

    Problem was in the PHP now it works very well

    thanks!

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj View Post
    Here is my new query:
    you have the right idea but i would clean up the SELECT clause with table qualification as follows:
    Code:
    SELECT label.descr_lbl
         , label.qty
         , c.registrations
         , label.qty -
           COALESCE(c.registrations,0) AS standby
      FROM label 
    LEFT OUTER
      JOIN ( SELECT breChoice_reg
                  , COUNT(*) AS registrations
               FROM Meet_reg
             GROUP 
                 BY breChoice_reg ) AS c
        ON c.breChoice_reg = label.id_lbl
     WHERE label.group_lbl = 7
       AND label.qty >= COALESCE(c.registrations,0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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