Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    1

    Unanswered: Need help with two queries

    Hello everyone. I have a question. What is the best way to implement the following two queries using MySQL.

    A table called PEOPLE containing "people"

    id - number
    id_group group number
    id_type - type

    Table called GROUP containing "groups of people"

    id - number
    name group name
    dt date of creating

    I need to output the following information with 2 queries:

    1. id ("number"), name ("groupname"), dt ("date"), number of people ("numofpeople") - only groups not containing people with NULL type (but can contain zero people).

    2. The same thing, but amount of people is counted without any resitriction on people with NULL type. All counted.

    Please, if that is possible, comment out the code.

    Great thanks! Excuse my English.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    will one query be okay instead of two?
    Code:
    SELECT g.id
         , g.name
         , COUNT(p.id_group) AS numofpeople
         , COUNT(p.id_type) AS numofpeopleNOTNULL
      FROM `group` AS g
    LEFT OUTER
      JOIN people AS p
        ON p.id_group = g.id
    GROUP
        BY g.id
         , g.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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