Skip site navigation (1) Skip section navigation (2)

Selecting non-existing rows?

From: Jan Danielsson <jan(dot)m(dot)danielsson(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Selecting non-existing rows?
Date: 2007-02-11 18:46:46
Message-ID: 45CF6496.3020300@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello,

   I have a table containing date column (dt), category, amount of money
transferred. Someone on this list helped me put this together:

--------------------------
SELECT days.dt,SUM(COALESCE(amount,0)) AS dayspend FROM
  (SELECT (SELECT MIN(dt) FROM transactions) + n AS dt
   FROM generate_series(0,(SELECT current_date)-(SELECT MIN(dt) FROM
transactions)) AS n) AS days
        LEFT JOIN transactions ON (transactions.dt = days.dt)
 GROUP BY days.dt
 ORDER BY days.dt;
--------------------------

   This does exactly what I want it to. Given a start date, it returns a
list of date followed by total sum that day, with the special perk that
it includes those days that no transaction was performed at all (which
allows taking an average and drawing graphics, etc). I tried breaking it
down, and I thought that I kind of understood it.

   The innermost query creates a list if ordinals. 0, 1, 2, 3, ....
These are added to the start date to create a list of dates from the
earliest date to the current date. I assume that COALESCE(amount,0) is
part of the important magic here -- it returns 0 of amount is NULL.

   Now I would like to create a "sum by month" (or week, for that matter
-- but one give the other). I know about date_part('year', dt) and
date_part('month', dt). But I can not get the query to work.

   I tried this:
--------------------------
SELECT date_part('year',days.dt) AS year,date_part('month',days.dt) AS
month,SUM(COALESCE(amount,0)) AS monthspend FROM
  (SELECT (SELECT MIN(dt) FROM transactions) + n AS dt
   FROM generate_series(0,(SELECT current_date)-(SELECT MIN(dt) FROM
transactions)) AS n) AS days
        LEFT JOIN transactions ON (transactions.dt = days.dt)
 WHERE cat_id=2
 GROUP BY year,month
 ORDER BY year,month;
--------------------------

   The cat_id is simply there to make sure that I'm summing up a
category which does not have entries each week in the range. This returns:

 year | month | monthspend
------+-------+------------
 2006 |     9 |     142.70
 2006 |    11 |     171.20
 2006 |    12 |      19.00
 2007 |     1 |     129.70

   I *want* it to return:

 year | month | monthspend
------+-------+------------
 2006 |     9 |     142.70
 2006 |    10 |       0.00
 2006 |    11 |     171.20
 2006 |    12 |      19.00
 2007 |     1 |     129.70
 2007 |     2 |       0.00

   What am I missing?

-- 
Kind regards,
Jan Danielsson


pgsql-novice by date

Next:From: Ron ArtsDate: 2007-02-12 00:23:13
Subject: partial indexed not being used.
Previous:From: Frank BaxDate: 2007-02-11 00:41:56
Subject: Re: Querying many to many relations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group