Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B3E30635BED for ; Thu, 19 Nov 2009 11:14:03 -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 64791-09 for ; Thu, 19 Nov 2009 15:13:52 +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 3D1DF634536 for ; Thu, 19 Nov 2009 11:11:14 -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 4R8T4RML; Thu, 19 Nov 2009 15:11:11 -0000 Message-ID: <53238C6176194626A9FA68BC3F6A9ECB@marktestcr.marktest.pt> From: "Oliveiros C," To: "Another Trad" Cc: References: <971C9BA25C4948E08D128793220A7DEE@marktestcr.marktest.pt> Subject: Re: need nelp with aggregate functions Date: Thu, 19 Nov 2009 15:11:10 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0027_01CA692A.86FA8B70" 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/60 X-Sequence-Number: 33411 This is a multi-part message in MIME format. ------=_NextPart_000_0027_01CA692A.86FA8B70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Howdy, It is quite simple, the joins you used would output a long list of pairs (client,computer), and, as many people has more than one computer, = many clients will appear repeated, but the COUNT keyword will count them = more than once though. The DISTINCT keyword prevents one client from appearing more than = once... It's basically that... Hope this helped Best, Oliveiros ----- Original Message -----=20 From: Another Trad=20 To: Oliveiros C,=20 Cc: pgsql-sql@postgresql.org=20 Sent: Wednesday, November 18, 2009 5:37 PM Subject: Re: [SQL] need nelp with aggregate functions ok, I did: SELECT count(DISTINCT c.cliente_id) as = qtd_client,count(cm.cm_id) as qtd_computers GREAT. It works. Please, explain me why and how it works, I wanna learn and do by = myself next time :) 2009/11/18 Oliveiros C, 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_0027_01CA692A.86FA8B70 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Howdy,
 
It is quite simple, the joins you used = would output=20 a long list of
pairs (client,computer), and, as many = people has=20 more than one computer, many clients will appear repeated, but the COUNT = keyword=20 will count them more than once though.
 
The DISTINCT keyword prevents one = client from=20 appearing more than once...
 
It's basically that...
 
Hope this helped
 
Best,
Oliveiros
 
----- Original Message -----
From:=20 Another=20 Trad
To: Oliveiros C,
Sent: Wednesday, November 18, = 2009 5:37=20 PM
Subject: Re: [SQL] need nelp = with=20 aggregate functions

ok, I did: SELECT count(DISTINCT c.cliente_id) as=20 qtd_client,count(cm.cm_id) as qtd_computers
GREAT. It = works.
Please,=20 explain me why and how it works, I wanna learn and do by myself next = time=20 :)


2009/11/18 Oliveiros C, <oliveiros.cristina@marktes= t.pt>
Try substituting the SELECT = count(c) as=20 qtd_client,count(cm) as qtd_computers
 
by
 
SELECT count( DISTINCT = c.cliente_id) as=20 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
 
-----=20 Original Message -----
From:=20 Another Trad
To:=20 pgsql-sql@postgresql.org
Sent:=20 Wednesday, November 18, 2009 4:55 PM
Subject:=20 [SQL] need nelp with aggregate functions

The DB structure is in attachment.
I with the = number of=20 clients and the number of computers that have processors with=20 "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=20 working
anyone can help me?


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

------=_NextPart_000_0027_01CA692A.86FA8B70--