i have those tables
table1
| id | name |
| 1 | axe |
| 2 | bow |
| 3 | car |
| 4 | dart |
table2 table3
| t1_id | number | | t1_id | letter |
| 1 | 5 | | 1 | a |
| 1 | 6 | | 1 | b |
| 1 | 2 | | 1 | c |
| 2 | 2 | | 2 | a |
| 2 | 2 | | 2 | c |
| 2 | 3 | | 2 | r |
| 3 | 8 | | 3 | y |
| 3 | 3 | | 3 | i |
| 3 | 1 | | 3 | a |
| 4 | 8 | | 4 | a |
| 4 | 9 | | 4 | b |
| 4 | 10 | | 4 | c |
and table1(id) is linked with table2(t1_id), table3(t1_id)
i run it to get them order by the highest letter_count match then by the highest average_number match to get this proper result http://www.sqlfiddle.com/#!9/69086b/8/0
SELECT
t1.id,
t1.name
FROM
table1 t1
INNER JOIN
table2 t2
ON t2.t1_id = t1.id
LEFT JOIN
table3 t3
ON t3.t1_id = t1.id
AND t3.letter IN ('a', 'b', 'c')
GROUP BY
t1.id
ORDER BY
COUNT(t3.letter) DESC,
AVG(t2.number) DESC
| id | name |
| 4 | dart |
| 1 | axe |
| 2 | bow |
| 3 | car |
and everything is working ok
but when i wanted to check if there is any problems with the query i decided to check the letter_count and avg_number so i used this query
SELECT
t1.id,
t1.name,
COUNT(t3.letter) AS letter_count,
AVG(t2.number) AS avg_number
FROM
table1 t1
INNER JOIN
table2 t2
ON t2.t1_id = t1.id
LEFT JOIN
table3 t3
ON t3.t1_id = t1.id
AND t3.letter IN ('a', 'b', 'c')
GROUP BY
t1.id
ORDER BY
letter_count DESC,
avg_number DESC
what i expected the result to be was
| id | name | letter_count | avg_number |
| 4 | dart | 3 | 9 |
| 1 | axe | 3 | 4.3333333333 |
| 2 | bow | 2 | 2.3333333333 |
| 3 | car | 1 | 4 |
but the result i got was http://www.sqlfiddle.com/#!9/69086b/3/0
| id | name | letter_count | avg_number |
| 4 | dart | 9 | 9 |
| 1 | axe | 9 | 4.3333333333 |
| 2 | bow | 6 | 2.3333333333 |
| 3 | car | 3 | 4 |
am i supposed to be worried on how the letter_count is multiplied three times on the future or does this just happens when i want to select the values and nothing bad is going to happen if i just kept it in ORDER BY?
i just want them to be ordered correctly which is what i got but checking the values showed me that letter_count is multiplied many times so i am confused about its performance or the letter_count can be ignored and the performance wont be affected?
