X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 1ECDF9DCC17 for ; Mon, 28 Nov 2005 14:12:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 80230-05 for ; Mon, 28 Nov 2005 14:12:53 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from tomts40-srv.bellnexxia.net (tomts40.bellnexxia.net [209.226.175.97]) by postgresql.org (Postfix) with ESMTP id 0D79E9DCC0D for ; Mon, 28 Nov 2005 14:12:51 -0400 (AST) Received: from acer.sympatico.ca ([70.50.50.247]) by tomts40-srv.bellnexxia.net (InterMail vM.5.01.06.10 201-253-122-130-110-20040306) with ESMTP id <20051128181251.OSIH1799.tomts40-srv.bellnexxia.net@acer.sympatico.ca> for ; Mon, 28 Nov 2005 13:12:51 -0500 Message-Id: <5.2.1.1.0.20051128130343.00a324d0@pop6.sympatico.ca> X-Sender: b1wudu74@pop6.sympatico.ca X-Mailer: QUALCOMM Windows Eudora Version 5.2.1 Date: Mon, 28 Nov 2005 13:06:28 -0500 To: From: Frank Bax Subject: Re: Anual Query In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Originating-IP: [0] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/261 X-Sequence-Number: 23512 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.