File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL question" Watch "SQL question" New topic
Author

SQL question

Swerrgy Smith
Ranch Hand

Joined: Mar 26, 2010
Posts: 88
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

Joined: Jun 21, 2008
Posts: 423
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

Joined: Mar 26, 2010
Posts: 88
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

Joined: Jun 21, 2008
Posts: 423
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL question
 
Similar Threads
Bumper car program
how to import custom made class in source file
Creating multi object in a loop without using list
Using final
hashCode and equals implementation