This week's book giveaway is in the Android forum.
We're giving away four copies of Head First Android and have Dawn & David Griffiths on-line!
See this thread for details.
The moose likes JDBC and Relational Databases 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


Win a copy of Head First Android this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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: 249

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: 2168
    
  22

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: 249

Using JOIN i got the correct query. thanks chris.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: using group by clause wih multiple tables
 
It's not a secret anymore!