How do I join rows in Postgres?

Question

I have a table of reservations in Postgres and I want to generate a table that has a row per month and shows earnings (and a lot of other things left out here for simplicity) for each year in a column.

I can do it by hard coding years, but there must be a better way. How do I do this to scale to x number of years?

Thanks!

CREATE TABLE reservations (
    checkin date NOT NULL,
    earnings integer
-- other data fields omitted 
  );

CREATE OR REPLACE FUNCTION compareYears()
RETURNS TABLE(month double precision, earnings_2016 bigint, earnings_2017 bigint)
AS
$$
BEGIN
RETURN QUERY
with 
r2017 as (SELECT
  date_part('month', reservations.checkin) AS month,
  sum(reservations.earnings) as earnings_2017
  FROM cd.reservations
  WHERE date_part('year', reservations.checkin) = 2017
  GROUP by date_part('month', reservations.checkin)),
r2016 as (SELECT
  date_part('month', reservations.checkin) AS month,
  sum(reservations.earnings) as earnings_2016
  FROM cd.reservations
  WHERE date_part('year', reservations.checkin) = 2016
  GROUP by date_part('month', reservations.checkin))
SELECT r2017.month, r2016.earnings_2016, r2017.earnings_2017
FROM r2016, r2017
WHERE r2017.month = r2016.month;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

It might be silly, but why don't you create a calendar table with year in it ? That way you could join on year, couldn't you ? — Arkhena, May 09 '17 at 05:07

score 0 · Answer 1 · edited May 23 '17 at 11:54

0

Have a look at crosstab. I think its exactly what you need. Here's an example

edited May 23 '17 at 11:54

Community

1
1

answered May 09 '17 at 09:24

Anand A

71
3

How do I join rows in Postgres?

1 Answers1