pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Anual Query
Date: Mon, 28 Nov 2005 13:06:28 -0500
Message-ID: <5.2.1.1.0.20051128130343.00a324d0@pop6.sympatico.ca> (raw)
In-Reply-To: <KPEAKEDEHEPGJLOLEDMBEEJNCDAA.mfacontacto@ono.com>

At 11:20 AM 11/28/05, Mauricio Fernandez A. wrote:
>Can any of you help me with this query?,  I need to retrieve the
>sum(units) and sum(cost) for each client in every month in the same
>row, something like this:
>
>client|UnJan|CostJan|UnFeb|CostFeb|UnMar|CostMar ...    |UnDec|CostDec
>- --------------------------------------------------------------------
>100058|580      |47384  |746  |87438  |536  |21653      ...     |857 
>|754875 (wrong
>data, just to show)
>
>Now I get this result using several querys (one for month and worse
>for client too) but this, of course its too expensive.
>
>Here is a table sample.
>
>  orderId  | client  | date        | product | Un | cost
>- ------------------------------------------------------
>  1001299  | 100058  | 20050103  | 759936  | 1  | 2375
>  1001299  | 100058  | 20050103  | 759928  | 1  | 2375
>  1001300  | 100078  | 20050103  | 759936  | 1  | 2375
>  1001301  | 100017  | 20050103  | 759928  | 1  | 2375
>  1001302  | 100009  | 20050103  | 759936  | 2  | 4750
>  1001302  | 100009  | 20050203  | 756304  | 1  | 6669
>  1001303  | 100009  | 20050203  | 759936  | 2  | 4750
>  1001304  | 100005  | 20050203  | 759936  | 20  | 47500
>  1001306  | 100013  | 20050203  | 759936  | 2  | 4750
>  1001306  | 100013  | 20050203  | 759928  | 2  | 4750
>  1001307  | 100013  | 20050203  | 759936  | 4  | 9500
>  1001308  | 100013  | 20050203  | 759936  | 2  | 4750
>  1001309  | 100050  | 20050303  | 759936  | 2  | 4750
>  1001310  | 100050  | 20050303  | 759936  | 4  | 9500


Search google and/or archives of this and/or other pgsql lists for 
"crosstab" and/or "pivot table".  I think there might also be something in 
contrib/ that could help. 




view thread (2+ messages)

Message-ID: <5.2.1.1.0.20051128130343.00a324d0@pop6.sympatico.ca>
Permalink:  ../5.2.1.1.0.20051128130343.00a324d0@pop6.sympatico.ca/
Also on:    postgresql.org/message-id/5.2.1.1.0.20051128130343.00a324d0@pop6.sympatico.ca

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: fbax@sympatico.ca
  Subject: Re: Anual Query
  In-Reply-To: <5.2.1.1.0.20051128130343.00a324d0@pop6.sympatico.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox