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

cdubks88 · #1 (**permalink**) 08-13-04, 16:50

.....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

Pat Phelan · #2 (**permalink**) 08-13-04, 17:36

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

cdubks88 · #3 (**permalink**) 08-13-04, 18:36

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

cdubks88 · #4 (**permalink**) 08-19-04, 12:57

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

Pat Phelan · #5 (**permalink**) 08-20-04, 00:59

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

Internet Services

Web Development

Digital Marketing

Consumer Tech