Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id EB01B6364FE for ; Wed, 18 Nov 2009 13:17:04 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 83400-08 for ; Wed, 18 Nov 2009 17:16:58 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id AC1F76353AD for ; Wed, 18 Nov 2009 13:16:55 -0400 (AST) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id 4R8T415Q; Wed, 18 Nov 2009 17:16:53 -0000 Message-ID: <971C9BA25C4948E08D128793220A7DEE@marktestcr.marktest.pt> From: "Oliveiros C," To: "Another Trad" , References: Subject: Re: need nelp with aggregate functions Date: Wed, 18 Nov 2009 17:16:52 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0019_01CA6872.EBFE1C20" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200911/47 X-Sequence-Number: 33398 This is a multi-part message in MIME format. ------=_NextPart_000_0019_01CA6872.EBFE1C20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Try substituting the SELECT count(c) as qtd_client,count(cm) as = qtd_computers by SELECT count( DISTINCT c.cliente_id) as qtd_client,count(/* put here the = primary key of the computer table */ ) as qtd_computers Then tell me if it output what you want Best, Oliveiros ----- Original Message -----=20 From: Another Trad=20 To: pgsql-sql@postgresql.org=20 Sent: Wednesday, November 18, 2009 4:55 PM Subject: [SQL] need nelp with aggregate functions The DB structure is in attachment. I with the number of clients and the number of computers that have = processors with "manufacturer" =3D "INTEL" and "speed" =3D "2GB" I am trying: select count(c) as qtd_client, count(cm) as qtd_computers from cliente c inner JOIN computer cm on (c.cliente_id =3D = cm.cliente_id) inner join processor p on (cm.processor_id =3D = p.processor_id)inner join speed s on (s.speed_id =3D p.speed_id)INNER = JOIN manufacturer m on (p.manufacturer_id=3Dm.manufacturer_id) where m.manufacturer =3D 'INTEL'and s.speed =3D '2GB'but is not working anyone can help me? -------------------------------------------------------------------------= ----- --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ------=_NextPart_000_0019_01CA6872.EBFE1C20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Try substituting the SELECT count(c) as = qtd_client,count(cm) as qtd_computers
 
by
 
SELECT count( DISTINCT c.cliente_id) as = qtd_client,count(/* put here the primary key of the computer table */ ) = as=20 qtd_computers
 
Then tell me if it output what you=20 want
 
Best,
Oliveiros
 
----- Original Message -----
From:=20 Another=20 Trad
Sent: Wednesday, November 18, = 2009 4:55=20 PM
Subject: [SQL] need nelp with = aggregate=20 functions

The DB structure is in attachment.
I with the number = of=20 clients and the number of computers that have processors with = "manufacturer" =3D=20 "INTEL" and "speed" =3D "2GB"
I am trying:
select =
count(c) as qtd_client, count(cm) as qtd_computers

  from cliente c
  inner JOIN computer cm on (c.cliente_id =3D =
cm.cliente_id)
  inner join processor p on (cm.processor_id =3D =
p.processor_id)
inner join speed s on (s.speed_id =3D =
p.speed_id)
INNER JOIN manufacturer m on =
(p.manufacturer_id=3Dm.manufacturer_id)

where m.manufacturer =3D 'INTEL'
and s.speed =3D '2GB'
but is = not=20 working
anyone can help me?


--
Sent via pgsql-sql mailing list=20 (pgsql-sql@postgresql.org)
To make changes to your=20 = subscription:
http://www.postgresql.org/mailpref/pgsql-sql
------=_NextPart_000_0019_01CA6872.EBFE1C20--