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