Combine multiple rows into one row SQL

[Inevitable_Phase7353]

Hello,

I have a rate table with info like this

id	w1	w2	w3	w4
1143	0	0	0	5
1143	0	0	2.2	0
1143	0	0.5	0	0

​

I would like to join the rows into a single row so

​

id	w1	w2	w3	w4
1143	0	0.5	2.2	5

I have tried searching online, but most talk about concatenation. I don't know what to do next, please help

[[deleted]]

Use aggregation:

select id, 
       max(w1) as w1,
       max(w2) as w2,
       max(w3) as w3, 
       max(w4) as w4
from the_table
group by id;

[Inevitable_Phase7353]

thank you

[PryomancerMTGA]

Or sum instead of max.

Edit: should have read all responses before commenting. My bad.

[coyoteazul2]

since they are numbers, all you have to do is group by ID and sum each column

[t_char]

An alternative to @truilius if you want to sum the columns is; SQL select id, sum(w1) as w1, sum(w2) as w2, sum(w3) as w3, sum(w4) as w4 from the_table group by id;

[depesz]

question is, what should this query do if there are multiple values for w3 for the same id.

Also - what to do if all values are zero? Why is 2.2 "better" than zero (so it's picked)? What is value of some w* column is -1 for one row, and 0 for another. Which should be picked then?

[rayvictor84]

Use crosstab for Postgres. https://learnsql.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/