I have multiple tables i wish to join up to produce one big query with specific columns summed. I was having a problem getting the correct summed columns in the final query (i believe this was because i was due to data being joined to many lines and summing after the join)

is the correct way to handle this type of problem to join on select statements? This is the way i achieved it but wasnt sure if this is the correct method?


from xyz as t1
    left join
        (select id_a, sum(occ) as sum_mc
        from t2
        group by id_a) as sel_t2
    on t1.id_fh = sel_t2.id_a
group by t1.id_a;
  • If xyz (id_fh) is a primary/unique key, you don't need the external group by. – ypercubeᵀᴹ Oct 11 '16 at 10:29

See if this gives you the correct values:

SELECT t1.id_a, 
        ( SELECT  sum(occ)
            from  t2
            WHERE  t2.id_a = t1.id_fh
            group by  t2.id_a 
        ) AS sum_mc
    FROM xyz AS t1

Note: I am assuming that t1.id_a is unique, so that the GROUP BY is not needed.

(It is confusing to have id_a in both tables, yet not be JOINing on it.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.