join instances of same row

Question

I have a table h containing data like this (OK, not really, it's just an example):

subj_id  q1  q2  q3  q4  q5  q6  num
      1   1   0   0   1   0   0    1
      1   0   0   0   1   0   0    2
      2   1   1   1   1   0   1    1
      2   1   0   0   1   0   0    2
      2   1   1   1   0   0   1    3
      3   0   1   0   0   1   1    1

I would like to sum up the q's for each subj_id resulting in a output like this:

subj_id   num1   num2   num3  
      1      2      1   null
      2      5      2      4
      3      3   null   null

but instead I get the following:

subj_id   num1   num2   num3
      1      2      1   null
      1      2      1   null
      2      5      2      4
      2      5      2      4
      2      5      2      4
      3      3   null   null

where the summed rows are repeated as many times as the subj_id appears in the table.

My query (postgres) looks like this:

select h.subj_id, n1.sum as num1, n2.sum as num2, n3.sum as num3 from ((( h
    left join (select subj_id, q1+q2+q3+q4+q5+q6 as sum from h where num=1) as n1 on h.subj_id=n1.subj_id)
    left join (select subj_id, q1+q2+q3+q4+q5+q6 as sum from h where num=2) as n2 on h.subj_id=n2.subj_id)
    left join (select subj_id, q1+q2+q3+q4+q5+q6 as sum from h where num=3) as n3 on h.subj_id=n3.subj_id) order by h.subj_id

Left join is obvious not the trick to use here, but what to do to skip the repeating rows?

Thanks in advance!

score 1 · Accepted Answer · edited May 23 '17 at 11:50

Your query could be easily modified to this:

with cte as (
    select subj_id, q1 + q2 + q3 + q4 + q5 + q6 as q, num
    from h
)
select
    subj_id,
    sum(case when num = 1 then q end) as num1,
    sum(case when num = 2 then q end) as num2,
    sum(case when num = 3 then q end) as num3
from cte
group by subj_id
order by subj_id

I think plan would be much better - no joins at all.

=> sql fiddle demo

Brief explanation why your query is not working and how you can improve it:

You receive more rows that you want because basically what your query does is selecting each row from table h and then join to it sum from table h with num = 1, 2, 3. You have 6 rows in your initial table and it's logical you'll have 6 rows in your result;
If you ever will make some query like this, I strongly suggest you to use aliases for tables in the inner queries. I'll help you to understand queries. In some cases it'll also help you to avoid incorrect results - see my answer in this topic - SQL IN query produces strange result.

-

select
    h.subj_id, n1.sum as num1, n2.sum as num2, n3.sum as num3
from h
    left join (
        select h1.subj_id, h1.q1+h1.q2+h1.q3+h1.q4+h1.q5+h1.q6 as sum
        from h as h1
        where h1.num=1
     ) as n1 on h.subj_id=n1.subj_id
    left join (
        select h2.subj_id, h2.q1+h2.q2+h2.q3+h2.q4+h2.q5+h2.q6 as sum
        from h as h2
        where h2.num=2
    ) as n2 on h.subj_id=n2.subj_id
    left join (
        select h3.subj_id, h3.q1+h3.q2+h3.q3+h3.q4+h3.q5+h3.q6 as sum
        from h as h3
        where h3.num=3
    ) as n3 on h.subj_id=n3.subj_id
order by h.subj_id

Your solution is both beautiful and absolutely correct, thank you very much for your help and for the link to Fiddle (and for adding the missing `sql` tag). As my solution may show, I'm not that much into sql. Could you - just for completeness - explain to me why the rows are duplicated in my script? Thanks! — Janus Engstrøm, Sep 06 '13 at 07:21
@JanusEngstrøm added some explanation, don't hesitate to ask if that's not enough — Roman Pekar, Sep 06 '13 at 07:42

join instances of same row

1 Answers1