The errors are in the example below not in the code that i'm working
with. the group by would be school_name or school_id otherwise the
query would error and I wouldn't be experiencing the oddities that I am.
What it seems to be is that if I want to preform math on 2 sum
functions, (+-*/) it will work if I select only one of the values in the
group by field. I desire to see a list of all the distinct values of
the group by field and the results of the math on the sum functions for
each value, this does not work correctly.
To me it seems like a bug in mysql, but I don't want to blame anybody
untill I'm sure that my query is constructed correctly.
Bob Hall wrote:
>> I'm gaving a problem with adding several sums together when using a
>> group by clause. The table I have looks like this:
>>
>> school_id int
>> school_name varchar
>> sport varchar
>> cost_a float(9,2)
>> cost_b float(9,2)
>> cost_c float(9,2)
>>
>> The real table is normalized, figured it would make for a simpler
>> example this way, so you don't have to tell me ;) Each school has
>> records for several diffrent sports I'm trying to query for the total
>> amount spent for each school with the following query.
>>
>> SELECT
>> school_name,
>> (sum(cost_a)+sum(cost_b)+sum(cost_c)) as total
>> FROM
>> schools
>> GROUP BY school
>
>
> Sir, the obvious error is that "school" is not a column in your table.
> Try grouping by school_id.
>
> Bob Hall
>
>>
>> With the desired result
>>
>> school_1 125000.00
>> school_2 234642.12
>> school_3 98433.45
>>
>>
>> But I get zero's for the total column. If I query for only one school
>> I get the correct numbers:
>>
>> SELECT
>> school,
>> (sum(cost_a)+sum(cost_b)+sum(cost_c)) as total
>> FROM
>> schools
>> WHERE
>> school='myschool'
>> GROUP BY school
>>
>> If I query for a few schools (school_id>3) sometimes I get unusual
>> numbers, 17.24 for one example. I'm not sure what's happening with
>> these queries so I have to ask.
>>
>> Am I doing the query wrong?
>> Is SQL or mySQL not able to handle this query?
>> Or did I find a bug in mySQL?
>>
>>
>> --
>> Jeff Bearer, RHCE
>> Webmaster
>> PittsburghLIVE.com
>>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
