Results 1 to 3 of 3

Thread: aggregate

  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Unanswered: aggregate

    Hello,
    I am a newbee as far as Postgres is concerned.
    I am stuck with a small issue.
    I have a result set like:

    ID Name Quantity
    1 a 10
    1 b 20
    1 c 10
    2 d 15
    2 e 10

    I want to display it like :

    ID Name Quantity Name1 Quantity1 Name2 Quantity2
    1 a 10 b 20 c 10

    What I want to acheive is set the records with the same id in a single row but with different headers so that I can easily use them in the report.

    Any help will be highly appreciated.

    Thanks & Regards
    Garry99

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    If the names a,b,c,d,e are a fixed set you can do something like:

    Code:
    SELECT id, 'a' AS name, SUM(CASE WHEN name = 'a' THEN quantity ELSE 0 END) AS quantity,
      'b' AS name1, SUM(CASE WHEN name = 'b' THEN quantity ELSE 0 END) AS quantity1
    ...
    FROM foo
    GROUP BY id
    But if it is any more complicated than that, you'll need the crosstab functionality found in the tablefunc contrib module.
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: tablefunc

  3. #3
    Join Date
    Jun 2010
    Posts
    2
    Hello artacus72,
    Thanks for the assistance.
    I will try the crosstab functionality as my query is a bit complex one.
    Will update you abt it.



    Thanks & Regards
    Garry99

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •