Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id BEB64633443 for ; Thu, 19 Nov 2009 12:16:42 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 70645-01 for ; Thu, 19 Nov 2009 16:16:22 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yw0-f198.google.com (mail-yw0-f198.google.com [209.85.211.198]) by mail.postgresql.org (Postfix) with ESMTP id E18666333D6 for ; Thu, 19 Nov 2009 12:16:30 -0400 (AST) Received: by ywh36 with SMTP id 36so2093549ywh.15 for ; Thu, 19 Nov 2009 08:16:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=9c4rk92wA7wvQrn/B64yVrIEMIaqvXLrr7+BD7rsU6o=; b=ZWYZ6DnKKyxf/npyc+4oyuS+7hzlTX5J96yhYtxzpQRqNnihI7Cqsy91LDhkGXXQEX AjaNDuT5QpIoccsg7JQk+HVHfStRUOgoLwbzcTtUoypuT4Cl1xUytX4Xu7n6TqDDB2yx LUY+CaUJMRDd69EIwIpHCL3RdLybXgzqBBh/k= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=R7j8xJWziNkz8hydko1fcMLEvSeVz6Q0zCL4GGLQRTyR0k94MhyfvSg5ixkG7NszM4 Toj31qjs1Rr8p/b7QaQ1/fsrQYA3iNHvK9c2Q81Z02yJRboTMnco246KwnHNigvCrJkK EIniYo90XZAl5HTLd9DUFZujtp/lFmztZ+VPA= MIME-Version: 1.0 Received: by 10.90.62.21 with SMTP id k21mr386305aga.10.1258647384711; Thu, 19 Nov 2009 08:16:24 -0800 (PST) In-Reply-To: <53238C6176194626A9FA68BC3F6A9ECB@marktestcr.marktest.pt> References: <971C9BA25C4948E08D128793220A7DEE@marktestcr.marktest.pt> <53238C6176194626A9FA68BC3F6A9ECB@marktestcr.marktest.pt> Date: Thu, 19 Nov 2009 13:16:24 -0300 Message-ID: Subject: Re: need nelp with aggregate functions From: Another Trad To: "Oliveiros C," Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001636283b964361580478bbae5a X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.004 tagged_above=-10 required=5 tests=AWL=0.594, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200911/61 X-Sequence-Number: 33412 --001636283b964361580478bbae5a Content-Type: text/plain; charset=ISO-8859-1 Thank you all very much. This "official" forum is really great. 2009/11/19 Oliveiros C, > 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 ----- > *From:* Another Trad > *To:* Oliveiros C, > *Cc:* pgsql-sql@postgresql.org > *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 ----- >> *From:* Another Trad >> *To:* pgsql-sql@postgresql.org >> *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" = "INTEL" and "speed" = "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 = cm.cliente_id) >> inner join processor p on (cm.processor_id = p.processor_id) >> inner join speed s on (s.speed_id = p.speed_id) >> INNER JOIN manufacturer m on (p.manufacturer_id=m.manufacturer_id) >> >> >> where m.manufacturer = 'INTEL' >> and s.speed = '2GB' >> >> but is not working >> anyone can help me? >> >> ------------------------------ >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> >> > --001636283b964361580478bbae5a Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Thank you all very much.
This "official" forum is really great= .

2009/11/19 Oliveiros C, <oliveiros.cristina= @marktest.pt>
Howdy,
=A0
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 ke= yword=20 will count them more than once=A0though.
=A0
The DISTINCT keyword prevents one clie= nt from=20 appearing more than once...
=A0
It's basically that...
=A0
Hope this helped
=A0
Best,
Oliveiros
=A0
----- Original Message -----
From:=20 Another=20 Trad
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, <= oliveir= os.cristina@marktest.pt>
Try substituting the SELECT count(= c) as=20 qtd_client,count(cm) as qtd_computers
=A0
by
=A0
SELECT count( DISTINCT c.cliente_i= d) as=20 qtd_client,count(/* put here the primary key of the computer table */ )= as=20 qtd_computers
=A0
Then tell me if it output what you= =20 want
=A0
Best,
Oliveiros
=A0
-----=20 Original Message -----
From:=20 Another Trad
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 numbe= r 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.client=
e_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 manufactu=
rer 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
=


--001636283b964361580478bbae5a--