Re: [SQL] need nelp with aggregate functions

[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

Re: [SQL] need nelp with aggregate functions

[Another Trad]

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
>>
>>
>