SQL create columns with sum of another column by unique items

Question

I am a little bit new to SQL. So hope you can help me with this question as I didnt find answer yet. I have joined tables from other sources and I need to create new columns based on unique values of one column with summ of another col. values.

For example, this is my table:

And this is what I need to get (Summ of A for each unique value in B added as a column):

For now I can do it manually like this:

SELECT 
    EM.[id]
    ,SUM(CASE WHEN AM.[B]='ZA' THEN EM.[A] ELSE 0 END) "ZA_sum_A"
    ,SUM(CASE WHEN AM.[B]='ZB' THEN EM.[A] ELSE 0 END) "ZB_sum_A"
FROM [xxx].x.x  AS AM
  INNER JOIN [yyy].[y].[y] AS EM ON (AM.ELEMENT = EM.ELEMENT)
  WHERE ...
GROUP BY EM.[id]

But issue is that I can have hundreds of unique values in B. So, question how to do it correctly..

I know, issue that now it's hardcoded each column manually. But it could be hundreds of types in B - a little bit unhealthy manually create each column.. — Alex_Y
– Alex_Y, Commented Mar 12, 2020 at 15:19

3N1GM4 · Accepted Answer · 2020-03-11 15:48:32Z

1

If you just want to see the summed values of EM.[A] for each combination of EM.[id] and AM.[B], you can do:

select
    em.[id],
    am.[B],
    sum(em.[A])
from [xxx].x.x as am
join [yyy].[y].[y] as em on am.ELEMENT = em.ELEMENT
where [...]
group by em.[id], am.[B]

If you then want to have the distinct values of AM.[B] appear as columns, so there is only one row for each distinct value of EM.[id], you either need to know what the distinct values of AM.[B] are and use PIVOT.

If the exact values of AM.[B] are not known, or change often over time, you'll need to do something dynamic, like in this answer, but in the opposite direction.

answered Mar 11, 2020 at 15:48

3N1GM4

3,3463 gold badges23 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

3N1GM4 Over a year ago

No problem, feel free to accept the answer if you found it useful.

Collectives™ on Stack Overflow

SQL create columns with sum of another column by unique items

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related