1

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

1 Answer 1

2

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
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.