Advertisement
FlavioNunes

Select Sum Postgres

Dec 14th, 2022
1,043
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2. rc.company_id,
  3. c.trading_name,
  4. SUM(rc.active) AS total_actives,
  5. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date)) as actives_m0,
  6. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '1' MONTH)) as actives_m1,
  7. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '2' MONTH)) as actives_m2,
  8. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '3' MONTH)) as actives_m3,
  9. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '4' MONTH)) as actives_m4,
  10. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '5' MONTH)) as actives_m5,
  11. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '6' MONTH)) as actives_m6,
  12. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '7' MONTH)) as actives_m7,
  13. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '8' MONTH)) as actives_m8,
  14. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '9' MONTH)) as actives_m9,
  15. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '10' MONTH)) as actives_m10,
  16. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '11' MONTH)) as actives_m11,
  17. SUM(rc.active) FILTER(WHERE date_trunc('month', rc.created_at)::date = date_trunc('month', current_date - INTERVAL '12' MONTH)) as actives_m12,
  18. FROM report.companies AS rc
  19. JOIN public.companies AS c ON rc.company_id = c.id
  20. GROUP BY rc.company_id, c.id
  21. ORDER BY rc.company_id
Tags: Postegres
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement