On Thu, Aug 18, 2016 at 10:56 AM, <haman...@t-online.de> wrote: > I have a table cv with custid and vendid columns. Every entry represents the > purchase of a product > available from a specific vendor. > Now, for a set of "interesting" vendors, I would like to select a new table > custid, c415, c983, c1256 > based upon part queries > select custid, count(vendid) as c415 from cv where vendid = 415 group by > custid ....
Divide and conquer, first you get the raw data ( so you have what you need as 'vertical' tagged columns ): ( beware, untested )... with raw_data as ( select custid, vendid, count(*) as c from cv where vendid in (415,983,1256) group by 1,2; ) Then put it in three columns ( transforming it into diagonal matrix ): , column_data as ( select custid, case when vendid=415 then c else 0 end as c415, case when vendid=983 then c else 0 end as c983, case when vendid=1256 then c else 0 end as c1256 from raw_data ) and then group then ( putting them into horizontal rows ): select custid, max(c415) as c415, max(c983) as c983, max(c1256) as c1256 from column_data group by 1; Note: I used 0 in else to get correct counts for the case where not al vendids are present. If you prefer null you can use it, IIRC max ignores them. Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
