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 using group by clause wih multiple tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "using group by clause wih multiple tables" Watch "using group by clause wih multiple tables" New topic
Author

using group by clause wih multiple tables

Astha Sharma
Ranch Hand

Joined: Oct 15, 2011
Posts: 245

Hi

I am trying to make a qurey for combining two tables using group by clause

tables are-

table 1-
department

deptid deptname
1 c
2 c++
3 java



employee
empid empsal deptid
1 1000 1
2 2000 2
3 3000 3
4 1000 1
5 2000 2
6 3000 3
7 1000 1
8 2000 2
9 3000 3
10 1000 1



now the query requires to make another table having two columns

deptname totalsal
c 4000
c++ 6000
java 9000


i'v tried a lot but not getting how to make this query. the query i made is

SELECT Department.DeptId,SUM(Employee.EmpSal) as TotalSal
FROM Department, Employee
GROUP BY Department.DeptId

but it is showing wrong result.

please help



Astha - OCPJP 6 (90%)
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1625
    
  13

Your GROUP BY is OK, but you haven't told your query how to link Employees to Departments - you need a join condition. If you don't include a join condition, you will get every possible combination of Employee and Department in your results.


No more Blub for me, thank you, Vicar.
Astha Sharma
Ranch Hand

Joined: Oct 15, 2011
Posts: 245

Using JOIN i got the correct query. thanks chris.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: using group by clause wih multiple tables
 
Similar Threads
problem with one to many relationship
simple example implementation?
Please help me in creation of tables
Oracle Forign Key Basic .
How to perform MultiTask in a Servlet