Sv: how to build this query ??? Please help !!!

Something like this oughtta work (not tested)

SELECT a_nr, 
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr) AS #cylinder,
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zdr') AS #zdr,
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zcu') AS #zcu,
(SELECT count(*) FROM cylinders WHERE z_a_nr = a_nr AND z_status = 'zcr') AS #zcr,
product, state
FROM orders


/Nikolaj

-----Oprindelig meddelelse-----
Fra: juerg.rietmann@pup.ch <juerg.rietmann@pup.ch>
Til: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Dato: 3. januar 2001 16:16
Emne: [SQL] how to build this query ??? Please help !!!


>Hello there
>
>I have a problem, building a query. Who can help ?
>I use postgreSQL 7.0.3.
>
>Thanks for any help and suggestions ... jr
>
>orders
>a_nr ¦ product ¦ state
>-----------------------------
>11   ¦ tp      ¦ aaaa
>12   ¦ fi      ¦ bbbb
>13   ¦ tp      ¦ cccc
>14   ¦ ok      ¦ eeee
>15   ¦ dm      ¦ aaaa
>
>cylinders
>z_a_nr   ¦     z_status
>------------------------------
>11     ¦  zdr
>11     ¦  zdr
>12     ¦  zdr
>12     ¦  zcu
>13     ¦  zdr
>13     ¦  zcu
>13     ¦  zcu
>13     ¦  zcr
>15     ¦  zcu
>15     ¦  zcu
>15     ¦  zdr
>
>I need a query for the following output :
>
>a_nr   ¦  #cylinder #zdr #zcu #zcr product    state
>----------------------------------------------------------------------
>11        2         2    0    0    dm         aaaa
>12        2         1    1    0    tp         aaaa
>13        4         1    2    1    fi         bbbb
>14        0         0    0    0    ok         eeee
>15        3         1    2    0    dm         aaaa
>
>
>
>
>
>============================================
>PFISTER + PARTNER, SYSTEM - ENGINEERING AG
>Juerg Rietmann
>Grundstrasse 22a
>6343 Rotkreuz
>Switzerland
>
>phone: +4141 790 4040
>fax: +4141 790 2545
>mobile: +4179 211 0315
>============================================
>
>