Mysql SUM output is wrong

I have a query like :

SELECT DISTINCT
g.thumb, h.hotel_name, h.id, COUNT(c.id) as total_comments, SUM(c.rating) AS total_ratings
FROM 
at_hotels h 
开发者_StackOverflowLEFT JOIN 
at_gallery g 
ON 
h.id = g.h_id
LEFT OUTER JOIN
at_comments c
ON
h.id = c.h_id AND c.status = '1'
GROUP BY h.id 
ORDER BY h.id DESC LIMIT 5

Query is working except SUM(rating). Sum command is giving more than expected. I also tried SUM command like this :

--query
(SELECT SUM(rating) FROM at_comments WHERE at_comments.h_id = at_hotels.id) AS total
--more query

and this is not worked too.

total must be : 13, but this is giving 36. Where is the problem?

Sample Data : (Comments Table)

id h_id rating
----------
1 | 1  |5
----------
2 | 1  |3
----------
3 | 1  |5

My Tables :

It's entirely dependent on your data (that we cannot see), but I'd suggest not relying on MySQL to do "the right thing" for your grouping, but to explicitly GROUP BY them:

SELECT 
MAX(g.thumb), h.hotel_name, h.id, COUNT(c.id) as total_comments, SUM(c.rating) AS total_ratings
FROM 
at_hotels h 
LEFT JOIN 
at_gallery g 
ON 
h.id = g.h_id
LEFT OUTER JOIN
at_comments c
ON
h.id = c.h_id AND c.status = '1'
GROUP BY h.id, h.hotel_name
ORDER BY h.id DESC LIMIT 5

Firstly, lose the DISTINCT - it isn't needed.
Secondly, format your query so it is readable, like this kind of formatting:

SELECT
  g.thumb,
  h.hotel_name,
  h.id,
  COUNT(c.id) as total_comments,
  SUM(c.rating) AS total_ratings
FROM at_hotels h 
LEFT JOIN at_gallery g ON h.id = g.h_id -- put join, tablee and on clause on same line
LEFT JOIN at_comments c ON h.id = c.h_id AND c.status = '1' -- removed OUTER: redundant
GROUP BY 1,2,3 
ORDER BY h.id DESC
LIMIT 5;

Thirdly (already done above), add in non-aggregate columns to GROUP BY - I prefer using numbers - it's clearer.

See how that goes.

I solved this problem.

The problem was the 'rating' row DATA TYPE.

Rating row datatype was 'SET' and the values 1,2,3,4,5.

I changed data type to the TINYINT, records are automatically changed like this :

5 -> 16
3 -> 4
5 -> 16

16 + 4 + 16 = 36

So, summing 'SET' data type is buggy or something?