Robert Henkel accidentally sent a reply to me instead of to you or to
the mailing list. In his reply he states that he tried your SELECT
statement and it worked fine. If so, then the problem is in the
normalized version, and is probably a simple error similar to the one
I pointed out. Post your actual tables and actual SELECT statement.
Bob Hall
>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
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
---------------------------------------------------------------------
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
