Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Unanswered: 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:
    Code:
    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?
    Attached Thumbnails Attached Thumbnails MODEL.jpg  

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    You aren't doing what you think you are doing.

    Code:
    SELECT COUNT(a), COUNT(b), COUNT(DISTINCT a), COUNT(DISTINCT b)
    FROM generate_series(1,100) a
    CROSS JOIN generate_series(1,3) b;
    
     count     count     count     count    
     --------  --------  --------  -------- 
     300       300       100       3

  3. #3
    Join Date
    Nov 2009
    Posts
    3
    ok, so I must use distinct to avoid duplicate lines.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Maybe, I don't know. It depends on what question you are trying to answer. My point was just that when you join tables and do a count them, you get a count of the resultant rows and not the original rows from the table.

  5. #5
    Join Date
    Nov 2009
    Posts
    3
    right, do you know any good website or link to a document when I can read advanced tips from aggregate functions and advanced querys?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •