Thread: 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?

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.

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

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

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

Thank you all very much.
This "official" forum is really great.