Q:

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?

A:

You didn't alias your subquery and your subquery doesn't select the field you're joining on in the main query. As other mentioned the order by in a subquery won't work.
I suspect you're looking for a result along the lines of:
SELECT *
FROM orbiting_group_types ogt
LEFT JOIN
(SELECT SUM(val), orbiting_group_type_id
 FROM report_orbiting_vals  rov
 WHERE rov.orbiting_group_type_id = 4
group by orbiting_group_type_id) as rov
ON ogt.id = rov.orbiting_group_type_id

Notice as rov, group by, the second column in the select of your subquery and the removal of order by.

A:

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.

