Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Posts
    1

    Unanswered: how to find count of model of car?

    I have a question about finding the count of model. I have two tables one for person and another for car they are linked by id which is in the car table . When i run the code it shows 2 owners of the model even though i have 2 same model different cars within the same user . How do i write the code so it says that there is one owner for the two different cars for the same model.

    here is the database for car
    CarRego Model Make ManufactureYear ID
    abc123 Van Toyota 2001 1
    abc234 Ute Honda 2005 1
    pio345 HatchbackMitsubishi1990 2
    elf234 Hatchback Ford 1996 3
    flo124 Hatchback Toyota 1992 4
    asc234 Hatchback Holden 1965 6
    xyz Hatchback Holden 1998 5
    avc Van Holden 1989 1


    here is the database for person

    ID FirstName LastName PersonalWealth
    1 Hello Alest $10,000.00
    2 Leeds United $20,000.00
    3 Middle Brough $30,000.00
    4 Arsenal Chelsey $40,000.00
    5 Manchester United $50,000.00
    6 Peter Mean $60,000.00
    7 Hello Coward $60,000.00

    here is the code that i have
    SELECT Count(Car.ID) AS CountOfid1, Car.Model
    FROM person INNER JOIN Car ON person.ID=Car.ID
    GROUP BY Car.Model;

    but it works with van saying 2 of them but in real there is only one owner how do i fix this??
    Help would be much appreciated

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Try this rollercoaster1:

    SELECT SQLDistinctModelPerson.Model, Count(SQLDistinctModelPerson.ID) AS CountOfid1
    FROM
    (
    SELECT DISTINCT Car.Model, Car.ID
    FROM Car
    ) AS SQLDistinctModelPerson
    GROUP BY SQLDistinctModelPerson.Model;

Posting Permissions

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