Check the example below. Trying to make a sql query which will work in Derby database
Check the example below. Trying to make a sql query which will work in Derby database
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.
what is your expected out
– Sinto
Aug 27 at 12:25
Why no B row in the result?
– jarlh
Aug 27 at 12:29
@jarlh its just an example, there can be B in the output.
– Kemari Kun
Aug 27 at 13:04
Also @Gimhani, thanks for the answer but I do not want to use where condition i.e. I don't want results just for A but for all the values in the matching columns. I have written about it in the description as well.
– Kemari Kun
Aug 27 at 13:05
1 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
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
From table 2, you need to sum up hours for A and get the result set (remove its duplicates as well). Then, you can join that result with table 1 to select only name=A.
– Gimhani
Aug 27 at 12:25