pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedneed nelp with aggregate functions
6+ messages / 3 participants
[nested] [flat]
* need nelp with aggregate functions
@ 2009-11-18 16:55 Another Trad <anothertrad@gmail.com>
2009-11-18 17:07 ` Re: need nelp with aggregate functions Scott Marlowe <scott.marlowe@gmail.com>
2009-11-18 17:16 ` Re: need nelp with aggregate functions Oliveiros C, <oliveiros.cristina@marktest.pt>
0 siblings, 2 replies; 6+ messages in thread
From: Another Trad @ 2009-11-18 16:55 UTC (permalink / raw)
To: pgsql-sql
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?
Attachments:
MODEL.jpg (image/jpeg, 20.7K) download
view image
* Re: need nelp with aggregate functions
2009-11-18 16:55 need nelp with aggregate functions Another Trad <anothertrad@gmail.com>
@ 2009-11-18 17:07 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2009-11-18 17:07 UTC (permalink / raw)
To: Another Trad <anothertrad@gmail.com>; +Cc: pgsql-sql
On Wed, Nov 18, 2009 at 9:55 AM, Another Trad <anothertrad@gmail.com> wrote:
> 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?
If you are getting an error message it's a good idea to post it as well.
However, first problem is that c and cm are tables, not fields. You
need to count some field from those tables.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: need nelp with aggregate functions
2009-11-18 16:55 need nelp with aggregate functions Another Trad <anothertrad@gmail.com>
@ 2009-11-18 17:16 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-11-18 17:37 ` Re: need nelp with aggregate functions Another Trad <anothertrad@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Oliveiros C, @ 2009-11-18 17:16 UTC (permalink / raw)
To: Another Trad <anothertrad@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: need nelp with aggregate functions
2009-11-18 16:55 need nelp with aggregate functions Another Trad <anothertrad@gmail.com>
2009-11-18 17:16 ` Re: need nelp with aggregate functions Oliveiros C, <oliveiros.cristina@marktest.pt>
@ 2009-11-18 17:37 ` Another Trad <anothertrad@gmail.com>
2009-11-19 15:11 ` Re: need nelp with aggregate functions Oliveiros C, <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 6+ messages in thread
From: Another Trad @ 2009-11-18 17:37 UTC (permalink / raw)
To: Oliveiros C, <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql
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 <anothertrad@gmail.com>
> *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
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: need nelp with aggregate functions
2009-11-18 16:55 need nelp with aggregate functions Another Trad <anothertrad@gmail.com>
2009-11-18 17:16 ` Re: need nelp with aggregate functions Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-11-18 17:37 ` Re: need nelp with aggregate functions Another Trad <anothertrad@gmail.com>
@ 2009-11-19 15:11 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-11-19 16:16 ` Re: need nelp with aggregate functions Another Trad <anothertrad@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Oliveiros C, @ 2009-11-19 15:11 UTC (permalink / raw)
To: Another Trad <anothertrad@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: need nelp with aggregate functions
2009-11-18 16:55 need nelp with aggregate functions Another Trad <anothertrad@gmail.com>
2009-11-18 17:16 ` Re: need nelp with aggregate functions Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-11-18 17:37 ` Re: need nelp with aggregate functions Another Trad <anothertrad@gmail.com>
2009-11-19 15:11 ` Re: need nelp with aggregate functions Oliveiros C, <oliveiros.cristina@marktest.pt>
@ 2009-11-19 16:16 ` Another Trad <anothertrad@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Another Trad @ 2009-11-19 16:16 UTC (permalink / raw)
To: Oliveiros C, <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql
Thank you all very much.
This "official" forum is really great.
2009/11/19 Oliveiros C, <oliveiros.cristina@marktest.pt>
> 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 <anothertrad@gmail.com>
> *To:* Oliveiros C, <oliveiros.cristina@marktest.pt>
> *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 <anothertrad@gmail.com>
>> *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
>>
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-11-19 16:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-18 16:55 need nelp with aggregate functions Another Trad <anothertrad@gmail.com>
2009-11-18 17:07 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-11-18 17:16 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-11-18 17:37 ` Another Trad <anothertrad@gmail.com>
2009-11-19 15:11 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-11-19 16:16 ` Another Trad <anothertrad@gmail.com>
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