Check the example below. Trying to make a sql query which will work in Derby database

Question

I have two tables.

Table 1

Name Month Hours
A    5     2
B    20    1
C    30    5
B    4     6
A    6     3

Table 2

Name    Hours2
A       5
B       6
A       8

Result Table

Name    Hours   Hours2  month
A       2       13      5
A       3       13      6
B       1       6       20
B       6       6       4

So what's happening here is, there is a join between both tables where condition is that the Name matches. And on individual table there should be group by to remove multiple entries but if I do that then need to use aggregate function there.

Using aggregate function on Hours and Hours2 will yield most of it but if I want to see month field values as unique values against it then it is not possible by group by and trying to do partition by did not work as derby does not support it.

I am not able to make a query that can do the above.

Answer 1

i think below will work for you

select t1.Name,t1.Hours,t2.Hours2,t1.month from  Table1 t1 
inner join                                           
( SELECT  
  Name,SUM(Hours2)  as Hours2
  from Table2 group by Name     
 ) t2 on t1.Name=t2.Name