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: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Til: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
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
>============================================
>
>
