CODEWITHSUNDEEP
sqlpostgresqlunionouter-joinself-join
AlbinoRhino
AlbinoRhino

Reputation: 497

How can I combine two virtual tables from one table, assigning half the data to a new column, without losing data

I have a table formatted as such

age height name
15 180 george
16 192 phil
20 148 lily
17 187 george
19 196 phil
24 147 lily
19 190 george
20 199 phil
22 148 lily
21 190 george
27 197 phil
60 138 lily

I need to get the data into a format where a column exists for each name, which contains the height of the individual, with the row containing blanks or nulls for the other columns, except age and that users height

I have tried various combinations of unions and joins, but I dont seem to be able to get new columns with unions, and I dont seem to be able to join and retain data since in reality none of the ages overlap (and I need to preserve all data, so the ON clause is giving me trouble)

The closest query I have been able to get data from is

with aaa as (
  SELECT age, height as "george" from table1 where name in ($GRAFANA_VARIABLE1),
    bbb as SELECT age, height as "lily" from table1 where name in ($GRAFANA_VARIABLE2)
)
SELECT aaa.age, george, lily, from aaa FULL JOIN bbb on bbb.age = aaa.age

This results in

age george lily
15 180
NULL NULL 148
17 187 NULL
NULL NULL 147
19 190 NULL
NULL NULL 148
21 190 NULL
NULL NULL 138

which loses my age data for lily. I know Im close, I can also retain the age data in a 2nd column with another query where I rename bbb's age to age2, but for the purposes of the plotting tool I am using, the x-axis data must all exist in a single column, with each series on the y axis being its own column as well

Upvotes: -3

Views: 193

Answers (1)

AlbinoRhino
AlbinoRhino

Reputation: 497

The code

Select age, 
       Sum(Case When name='$GRAFANA_VARIABLE1' Then height Else NULL End) As George,
       Sum(Case When name='$GRAFANA_VARIABLE2' Then height Else NULL End) As Lily
From table1 WHERE name in ($GRAFANA_VARIABLE1) or name in ($GRAFANA_VARIABLE2)
Group by age
Order by age

did the trick for me. Im not sure how the sums would output if items were not unique.

Based off of answers found here: Helpful question and answer

Upvotes: 0

Related Questions