CODEWITHSUNDEEP
sqlpostgresqlcalculated-columnssql-null
Alex
Alex

Reputation: 59

SQL : duplicate columns with NULL values

I'm trying to get the number of quantities per month and per application taking into account a weighting:

SELECT jan.app_name, fev.app_name, jan.api, fev.api, jan.user_id, fev.user_id, qt_janvier, qt_fevrier
from(select
  app_name
, user_id
, api
, SUM(nb)
, SUM(nb) * CASE api
      WHEN 'v1.ects' THEN 0.7
      WHEN 'v1.uri' THEN 1
      WHEN 'v1.collection' THEN 1
      WHEN 'v1.id' THEN 1.7
      ELSE NULL
END  as qt_janvier
FROM stat_y2022m01
WHERE app_name IN (
  'TGE', 
  'ART',
  'MOSE'
)
AND user_id IN(
'30112'
,'28147'
,'30113'
)
GROUP BY 
  app_name
, api
, user_id 
order by app_name asc, qt_janvier)as jan

FULL JOIN

(select
  app_name
, user_id
, api
, SUM(nb)
, SUM(nb) * CASE api
      WHEN 'v1.ects' THEN 0.7
      WHEN 'v1.uri' THEN 1
      WHEN 'v1.collection' THEN 1
      WHEN 'v1.id' THEN 1.7
      ELSE NULL
END  as qt_fevrier
FROM stat_y2022m02
WHERE app_name IN (
  'TGE', 
  'ART',
  'MOSE'
)
AND user_id IN(
'30112'
,'28147'
,'30113'
)
GROUP BY 
  app_name
, api
, user_id 
order by app_name asc, qt_fevrier) as fev

on jan.app_name=fev.app_name and jan.user_id = fev.user_id and jan.api=fev.api
order by jan.app_name asc, qt_janvier desc

I have an ugly result, because for some api the sum(nb) is null :

app_name app_name    api     api    user_id  user_id    qt_janvier  qt_fevrier
  TGE      NULL     v1.ects  NULL    30112     NULL      24         NULL
  NULL     TGE      NULL     v1.uri  NULL     28147      NULL       161

Do you know a way to have only one app_name column, one api column and one user_id column and that NULL values can only be found in the qt_janvier and qt_fevrier columns?

I would like something like that:

app_name   api      user_id   qt_janvier    qt_fevrier
  TGE      v1.ects     30112      24        NULL
  TGE      v1.uri      28147      NULL       161

Upvotes: 1

Views: 256

Answers (1)

Andronicus
Andronicus

Reputation: 26056

You can use coalesce - this will take the first non-null value:

select coalesce(jan.app_name, fev.app_name) app_name, coalesce(jan.api, fev.api) api, coalesce(jan.user_id, fev.user_id) user_id, qt_janvier, qt_fevrier

Upvotes: 2

Related Questions