Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 14C8E633AE1 for ; Wed, 18 Nov 2009 13:37:27 -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 86158-02 for ; Wed, 18 Nov 2009 17:37:20 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f218.google.com (mail-gx0-f218.google.com [209.85.217.218]) by mail.postgresql.org (Postfix) with ESMTP id A2423632ADB for ; Wed, 18 Nov 2009 13:37:18 -0400 (AST) Received: by gxk10 with SMTP id 10so1261963gxk.3 for ; Wed, 18 Nov 2009 09:37:17 -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=YSXS9RwLnA3AkemUsUeFWc08P9vzQ4RRJV1y1qYqZVg=; b=qo2P+Lz/9w036kX9mJ4m1nWfDpOV9UIOqlNDjZXH7XmTGT+jhq86++Yk5BVRZpa5Ip gG1wMkJhU4gzU7qQZLXshJh4KccHZRwUaGbtMtXisY9Qqjb+4gxgten1Clo5DMUGAgwH XmgUDZoJGD7eFUJwOBSIxApzwiqe9OFJNA2Ko= 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=gzHH9fzgEBc7OuUvYwdM4ny3kLV35U8ISWDJELFfh64Ytngc5cNqiE5GXgtRE+UxoS iJ9UWVzu5amOLa3Bx0t4N692M6Rj4uSviZv2PRsAuaHK6+upmftC3D76E/sgybKasp7j dUQ+sOCW1/QbjthIe8jpRO7ceX/HIqe5jD8PM= MIME-Version: 1.0 Received: by 10.91.106.5 with SMTP id i5mr2639059agm.75.1258565837569; Wed, 18 Nov 2009 09:37:17 -0800 (PST) In-Reply-To: <971C9BA25C4948E08D128793220A7DEE@marktestcr.marktest.pt> References: <971C9BA25C4948E08D128793220A7DEE@marktestcr.marktest.pt> Date: Wed, 18 Nov 2009 14:37:17 -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=0016367f92e6acc0350478a8b188 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200911/48 X-Sequence-Number: 33399 --0016367f92e6acc0350478a8b188 Content-Type: text/plain; charset=ISO-8859-1 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 > > --0016367f92e6acc0350478a8b188 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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, <oliveiros.cristina@marktest.pt>
Try substituting the SELECT count(c) a= s=20 qtd_client,count(cm) as qtd_computers
=A0
by
=A0
SELECT count( DISTINCT c.cliente_id) a= s=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
----- Original Message -----
From:=20 Another=20 Trad
Sent: Wednesday, November 18, 2009= 4:55=20 PM
Subject: [SQL] need nelp with aggr= egate=20 functions

The DB structure is in attachment.
I with the number of= =20 clients and the number of computers that have processors with "manuf= acturer" =3D=20 "INTEL" and "speed" =3D "2GB"
I am tryin= g:
sel=
ect 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=20 (pgsql-sql@= postgresql.org)
To make changes to your=20 subscription:
http://www.postgresql.org/mailpref/pgsql-sql

--0016367f92e6acc0350478a8b188--