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
>============================================
>
>