Skip to content

Instantly share code, notes, and snippets.

@reinaldons
Created March 2, 2011 20:26
Show Gist options
  • Save reinaldons/851672 to your computer and use it in GitHub Desktop.
Save reinaldons/851672 to your computer and use it in GitHub Desktop.
PostgreSQL - Grouping all client subscriptions and multiply unitary value by quantity only when product ID is not 64
SELECT
client.id,
client.name,
fop.start_effective_date,
fop.end_effective_date,
EXTRACT(month FROM fop.end_effective_date)::integer AS month,
SUM(CASE subscription.product_id WHEN 64 THEN subscription.unit_value ELSE subscription.unit_value * subscription.qty END) AS total
FROM
master
LEFT JOIN
subscription ON subscription.client_id = client.id
LEFT JOIN
fop ON fop.client_id = client.id
WHERE
EXTRACT(year FROM fop.end_effective_date) = 2011
GROUP BY
client.id, client.nome, month, fop.start_effective_date, fop.end_effective_date
ORDER BY
month
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment