Skip to content

Instantly share code, notes, and snippets.

@andrewscaya

andrewscaya/case_filter.sql

Created November 4, 2016 18:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save andrewscaya/07b74eeaaf009b1421e5fdf19c3dd06e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save andrewscaya/07b74eeaaf009b1421e5fdf19c3dd06e to your computer and use it in GitHub Desktop.
Download ZIP
filter aggregate usage
Raw
case_filter.sql
SELECT
SUM(total) as total,
SUM(CASE WHEN collected IS TRUE THEN total END) as collected,
year
FROM
invoices
GROUP BY
year
Raw
filter.sql
SELECT
SUM(total) as total,
SUM(total) FILTER(WHERE collected IS TRUE) as collected,
year
FROM
invoices
GROUP BY
year
Raw
filter_ddl_data.sql
CREATE TABLE invoices (
total DECIMAL(10,2) NOT NULL,
collected BOOLEAN NOT NULL DEFAULT FALSE,
year INT NOT NULL
);
INSERT INTO invoices (total, collected, year)
VALUES
(15.04, FALSE, 2015),
(26.81, TRUE, 2016),
(22.12, FALSE, 2015),
(189.78, TRUE, 2016),
(329.70, TRUE, 2015),
(19.99, FALSE, 2016);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment