Re: Postgres SQL Query (Generating Date Groups) - Mailing list pgsql-general

  Mont, you have probably gotten an answer on this already, but I haven't
  seen one go across the list, so here goes.

  Assuming that I understand what you want, the easiest way that I can
  see to do it is by using self joins.

  ie:

      SELECT a.cust_id ,
             sum(a.bytes) as period_1,
             sum(b.bytes) as period_2,
             sum(c.bytes) as period_3
             sum(d.bytes) as period_4,
             sum(e.bytes) as period_5
             sum(f.bytes) as period_6
        FROM table_a a, table_a b, table_a c, table_a d, table_a e, table_a f
       WHERE a.cust_id = b.cust_id       AND
             b.cust_id = c.cust_id       AND
             c.cust_id = d.cust_id       AND
             d.cust_id = e.cust_id       AND
             e.cust_id = f.cust_id       AND
             a.usage_date = '2001-03-01' AND
             b.usage_date = '2001-03-02' AND
             c.usage_date = '2001-03-03' AND
             d.usage_date = '2001-03-05' AND
             e.usage_date = '2001-03-06' AND
             f.usage_date = '2001-03-07'
    GROUP BY a.cust_id;


  This will work, but you will have to hardcode the query each time for the
  number of your periods.

  I hope that this helps.

--
Bill


On Thu, Mar 15, 2001 at 08:08:59PM -0700, Mont Erickson wrote:
> First of all, I apologize if I have posted to the wrong group...
>
> Here's the question.
>
> What would be the proper method, given the following table, to generate,
> with a single select query, a date grouped output:
>
> table_a
>
> cust_id | usage_date | bytes
> --------+------------+-------
>       1 | 2001-03-01 |   2578
>       1 | 2001-03-02 |   1234
>       2 | 2001-03-01 |  12345
>       1 | 2001-03-05 |   2578
>       1 | 2001-03-07 |   1234
>       2 | 2001-03-06 |  12345
>
> etc etc etc...
>
> I'm trying for output that would look something like this:
>
> cust_id | period_1 | period_2 | period_3
> --------+----------+----------+----------
>       1 | 12345678 | 457892   | 98765
>       2 |    10734 |   1037   |  8709
>
> ...etc etc etc...
>
> I have tried writing the query along the lines of the following, but
> based on the results I'm seeing, I'm on the wrong track:
>
> select
>    cust_id,
>    case when
>       date_part('day',usage_date) >= 1 and date_part('day',usage_date)
> <= 7
>    THEN sum(sum) END as period_1,
>    case when
>       date_part('day',usage_date) >= 2 and date_part('day',usage_date)
> <= 8
>    THEN sum(sum) END as period_2,
>    case when
>       date_part('day',usage_date) >= 3 and date_part('day',usage_date)
> <= 9
>    THEN sum(sum) END as period_3
> from table_a
> group by
>         cust_id
>
> Is this possible under Postgres?  What can I do to accomplish this
> without writing a separate select for each date period?  Thank you in
> advance!
>
> Mont Erickson
> ns_monterickson@hotmail.com  (remove "ns_" to reply)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

--
     _____
    / ___/___       | Bill Huff / bhuff@colltech.com
   / /__  __/       | Voice: (512) 263-0770 x 262
  / /__/ /          | Fax:   (512) 263-8921
  \___/ /ollective  | Pager: 1-800-946-4646 # 1406217
      \/echnologies |------[ http://www.colltech.com ] ------