From: Oliveiros C, <oliveiros.cristina@marktest.pt>
To: Another Trad <anothertrad@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: need nelp with aggregate functions
Date: Thu, 19 Nov 2009 15:11:10 -0000
Message-ID: <53238C6176194626A9FA68BC3F6A9ECB@marktestcr.marktest.pt> (raw)
References: <af7271950911180855n4ec2b0b1y484382a1123d903d@mail.gmail.com>
<971C9BA25C4948E08D128793220A7DEE@marktestcr.marktest.pt>
<af7271950911180937lc6998e4r9ab700a058a6ab7f@mail.gmail.com>
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, <oliveiros.cristina@marktest.pt>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@marktest.pt, anothertrad@gmail.com
Subject: Re: need nelp with aggregate functions
In-Reply-To: <53238C6176194626A9FA68BC3F6A9ECB@marktestcr.marktest.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox