How do I use multiple filters in a group by in postgres?

Question

Looking to create a reporting table that groups by date, but sums on several different filters.

Example, say I have multiple locations of a business and want to sum sales grouped by date.

So say source table

Date  |  Location  | Amount
===========================
Mon   |   One      | 10
Mon   |   Two      | 15
Mon   |   One      | 12
Tue   |   One      | 13
Tue   |   Three    | 17

I'd like to transform this into

Date  |  Location One Sales | Location Two Sales
================================================
Mon   |   22                | 15
Tue   |   13                | 0

Racking my brains on this right now and running into a wall.

Thanks!

Answer 1

You can use a CASE to conditionally select.

---

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE Sales
    ("Date" varchar(3), "Location" varchar(5), "Amount" int)
;

INSERT INTO Sales
    ("Date", "Location", "Amount")
VALUES
    ('Mon', 'One', 10),
    ('Mon', 'Two', 15),
    ('Mon', 'One', 12),
    ('Tue', 'One', 13),
    ('Tue', 'Three', 17)
;

Query 1:

SELECT   "Date"
         , sum(case "Location" when 'One' then "Amount" else 0 end) as "Location One Sales"
         , sum(case "Location" when 'Two' then "Amount" else 0 end) as "Location Two Sales"
FROM     Sales
GROUP BY "Date"

Results:

| Date | Location One Sales | Location Two Sales |
|------|--------------------|--------------------|
|  Mon |                 22 |                 15 |
|  Tue |                 13 |                  0 |