Received: from nevald.k-net.dk (nevald.k-net.dtu.dk [130.225.71.226]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f03FcZ791805 for ; Wed, 3 Jan 2001 10:38:35 -0500 (EST) (envelope-from Nikolaj@kampsax.dtu.dk) Received: from carlsberg.kampsax.dtu.dk (carlsberg.kampsax.dtu.dk [192.38.212.2]) by nevald.k-net.dk (Postfix) with SMTP id 450FAC123 for ; Wed, 3 Jan 2001 16:39:10 +0100 (CET) Received: (qmail 10439 invoked from network); 3 Jan 2001 15:39:10 -0000 Received: from commando.kampsax.dtu.dk (HELO commando) (192.38.212.143) by carlsberg.kampsax.dtu.dk with SMTP; 3 Jan 2001 15:39:10 -0000 Message-ID: <008701c0759b$8ebe7100$8fd426c0@commando.kampsax.dtu.dk> Reply-To: "Nikolaj Lundsgaard" From: "Nikolaj Lundsgaard" To: Subject: Sv: how to build this query ??? Please help !!! Date: Wed, 3 Jan 2001 16:40:54 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by mail.postgresql.org id f03Fca791806 X-Archive-Number: 200101/31 X-Sequence-Number: 692 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 Til: 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 >============================================ > >