Обсуждение: Anual Query

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



Hello,

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  | 23751001299  |
100058 | 20050103  | 759928  | 1  | 23751001300  | 100078  | 20050103  | 759936  | 1  | 23751001301  | 100017  |
20050103 | 759928  | 1  | 23751001302  | 100009  | 20050103  | 759936  | 2  | 47501001302  | 100009  | 20050203  |
756304 | 1  | 66691001303  | 100009  | 20050203  | 759936  | 2  | 47501001304  | 100005  | 20050203  | 759936  | 20  |
475001001306 | 100013  | 20050203  | 759936  | 2  | 47501001306  | 100013  | 20050203  | 759928  | 2  | 47501001307  |
100013 | 20050203  | 759936  | 4  | 95001001308  | 100013  | 20050203  | 759936  | 2  | 47501001309  | 100050  |
20050303 | 759936  | 2  | 47501001310  | 100050  | 20050303  | 759936  | 4  | 9500
 

....

Thanks for any idea.

Mauricio Fernández A.
Ingeniero de Sistemas
U. Autónoma de Manizales


-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.0.2i

iQA/AwUBQ4sgNc4irmZP1c6WEQLcPgCeLQNXphTXFXqVFZcKxrIO7aAfCEMAn397
jOO4lULVn/3bPLm6du7hGJeR
=YSsL
-----END PGP SIGNATURE-----

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.