Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    5

    Exclamation Unanswered: Querying for multiple SUM() values from a given table field.....

    .....I need help on how to code a query statement and/or statements that would allow me to query a mysql table based on a set field (tix) and get the sum of that field for every instance of another field, $tourdate.

    I would like to write the SUM(tix) as num_tix, then setup another variable called $availtix that would be:

    $availtix = 25 - $num_tix;

    The problem I have is figuring out how to do that when the $tourdate will vary depending on what's been inserted into the table.....

    Any ideas would be greatly appreciated.

    Chris

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Could you use something as simple as:
    Code:
    SELECT tourdate, Sum(tix) AS sold_tix, 25 - Sum(tix) as avail_tix
       FROM myTable
       GROUP BY tourdate
       ORDER BY tourdate
    -PatP

  3. #3
    Join Date
    Aug 2004
    Posts
    5
    Quote Originally Posted by Pat Phelan
    Could you use something as simple as:
    Code:
    SELECT tourdate, Sum(tix) AS sold_tix, 25 - Sum(tix) as avail_tix
       FROM myTable
       GROUP BY tourdate
       ORDER BY tourdate
    -PatP
    Pat,

    I don't know, but I can sure try it.....does the GROUP BY just categorize the output to the browser, or does it still have to be formatted within tables?

    Thanks!

    Chris

  4. #4
    Join Date
    Aug 2004
    Posts
    5
    Pat -

    I tried that but couldn't seem to get it to work right....I suspect it's because I really don't have a loop that reads through the table records and pulls the recordset data out except the one initial result.....(if that's correct at all).

    Again, I've read quite a bit on mysql and php, but I am still very new to this.

    Anyway, what I am really needing to do at this point:

    1. Query the mysql table (call it haunted) for SUM(tix) and tourdate;

    2. Read through each record for the SUM(tix) and then take that returned value and subtract it from 25 to verify the number of actual tix available (I was thinking something like SELECT SUM(tix) as num_tix, tourdate from haunted GROUP BY tourdate ORDER by tourdate); then do $availtix = 25 - num_tix;

    3. After I verify $availtix, then I need to be able to check that it's != 0, and if so, output SOLD OUT.

    4. Otherwise, I need to write $tourdate and $availtix into a table (for EVERY instance of each $tourdate in haunted.

    Does that make sense to anyone?

    I'd REALLY appreciate any help......

    Thanks in advance.

    Chris

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a bit of a SWAG, but try using:
    Code:
    SELECT tourdate
    ,  Sum(tix) AS sold_tix
    ,  CASE
          WHEN 25 = Sum(tix) THEN 'SOLD OUT'
          ELSE Cast(25 - Sum(tix) AS VARCHAR(8))
       END AS avail_str
       FROM haunted
       GROUP BY tourdate
       ORDER BY tourdate
    -PatP

Posting Permissions

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