Evgeniy Kleban Evgeniy Kleban - 2 months ago 6
MySQL Question

SQL left join with SUM

I need to join one table with another, where for each type of group in left table i got sum of this value types in second table, where group_type = 4.
I tried following:

SELECT *
FROM orbiting_group_types ogt
LEFT JOIN
(SELECT SUM(val)
FROM report_orbiting_vals rov
ORDER by TYPE
WHERE rov.orbiting_group_type_id = 4)
ON ogt.id = rov.orbiting_group_type_id


However it throw me an error -

syntax error at or near "WHERE"
LINE 8: WHERE rov.orbiting_group_type_id = 4)


What did i miss?

Answer

Try this:

SELECT *
FROM orbiting_group_types ogt
LEFT JOIN
(SELECT SUM(val)
FROM report_orbiting_vals  subrov
WHERE subrov.orbiting_group_type_id = 4
//ORDER by TYPE
) rov
ON ogt.id = rov.orbiting_group_type_id

1) You should name your subqueries to reference them. You can't make a reference from the outside of the subquery to the inside.

2) TYPE doesn't sound like a column name

3) ORDER BY goes after WHERE, always.

Comments