• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL question

 
Swerrgy Smith
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear all,

Supposed that we have a table Person that contains two column (Name, Car).
Supposed that we have 3 kind of car: Car1, Car2, Car3.
How can we count the number of each kind of car that a person have without using 3 different separated SQL queries like that:
Select Name, count(*) from Person where Car = 'Car1' Group By Name .
Select Name, count(*) from Person where Car = 'Car2' Group By Name .
Select Name, count(*) from Person where Car = 'Car3' Group By Name .

I would like to use only 1 SQL query to do this.
Thanks you.
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The easiest way:

If you want only one row for each person, use a case expression (conforming ANSI SQL-92)

Similar to the second query but using outer joins:

 
Swerrgy Smith
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ireneusz Kordal wrote:The easiest way:

If you want only one row for each person, use a case expression (conforming ANSI SQL-92)



Thank you, the second approach is exactly what I want, not the first approach.
However, I wonder if this approach works well with all database?
 
Ireneusz Kordal
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you, the second approach is exactly what I want, not the first approach.
However, I wonder if this approach works well with all database?

You must try. And read your database documentation.
Oracle support the above queries, as I remember from version 9i.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic