CODEWITHSUNDEEP
sqljoinview
Ram Prasad
Ram Prasad

Reputation: 23

SQL Query for Joining 2 tables

I have 2 tables here with following structure Table1 which contains the Estimated cost and Table2 which contains the Actual cost and Task field in the Table2 is a foreign key based on the Table1 Budget Task field, i need to write a query and view to get the following result table which provides me the sum of Estimated_Cost and sum of Actual_cost details of the category.

Table1:

+------+----------+----------------+
| Task | Category | Estimated_Cost |
+------+----------+----------------+
|    1 |     9100 |         100.00 |
|    2 |     9100 |          15.00 |
|    3 |     9100 |           6.00 |
|    4 |     9200 |           8.00 |
|    5 |     9200 |          11.00 |
+------+----------+----------------+

Table2:

+---------+------+-------------+
| Voucher | Task | Actual_Cost |
+---------+------+-------------+
|       1 |    1 |       10.00 |
|       2 |    1 |       20.00 |
|       3 |    1 |       15.00 |
|       4 |    2 |       32.00 |
|       5 |    4 |        8.00 |
|       6 |    5 |        3.00 |
|       7 |    5 |        4.00 |
+---------+------+-------------+

Result table:

+----------+----------------+-------------+
| Category | Estimated_Cost | Actual_Cost |
+----------+----------------+-------------+
|     9100 |         121.00 |       77.00 |
|     9200 |          19.00 |       15.00 |
+----------+----------------+-------------+

Upvotes: 1

Views: 133

Answers (5)

Teymur Gasimov
Teymur Gasimov

Reputation: 21

Try this

select at1.Category, at1.Estimated_Cost, at2.Actual_Cost from (
select t1.Category, sum(t1.Estimated_Cost) as Estimated_Cost from Table1 t1
group by t1.Category) at1
left join (
select t1.Category, sum(t2.Actual_Cost) as Actual_Cost from Table2 t2 join Table1 t1 on t2.Task = t1.Task
group by t1.Category) at2
on at1.Category = at2.Category

Upvotes: 0

Nitin
Nitin

Reputation: 528

select cat as Category,SUM(ecost) as Estimated_Cost,SUM(acost) as Actual_Cost from (SELECT table1.Estimated_Cost as ecost,table1.Category as cat,table2.Task,SUM(table2.Actual_Cost) as acost FROM `table2` join table1 ON
table2.Task = table1.task
GROUP by table2.Task)
as t11 
GROUP by cat

Upvotes: 1

Jagrut Sharma
Jagrut Sharma

Reputation: 4754

You can use this query:

select x.Category Category, y.Estimated_Cost Estimated_Cost, x.Actual_Cost Actual_Cost 
from
(
  select t1.Category Category, sum(a.Actual_Cost) Actual_Cost
  from Actuals a,
  (select distinct Category from Budget) t1
  where a.Task IN (Select Task from Budget where Category = t1.Category)
  group by t1.Category
) x,
(
  select Category, sum(Estimated_Cost) Estimated_Cost
  from Budget
  group by Category
) y
where x.Category = y.Category;

Illustration with provided data:

select * from Budget;
+------+----------+----------------+
| Task | Category | Estimated_Cost |
+------+----------+----------------+
|    1 |     9100 |         100.00 |
|    2 |     9100 |          15.00 |
|    3 |     9100 |           6.00 |
|    4 |     9200 |           8.00 |
|    5 |     9200 |          11.00 |
+------+----------+----------------+

select * from Actuals;
+---------+------+-------------+
| Voucher | Task | Actual_Cost |
+---------+------+-------------+
|       1 |    1 |       10.00 |
|       2 |    1 |       20.00 |
|       3 |    1 |       15.00 |
|       4 |    2 |       32.00 |
|       5 |    4 |        8.00 |
|       6 |    5 |        3.00 |
|       7 |    5 |        4.00 |
+---------+------+-------------+
 select x.Category Category, y.Estimated_Cost Estimated_Cost, x.Actual_Cost Actual_Cost
  -> from
  -> (
  ->   select t1.Category Category, sum(a.Actual_Cost) Actual_Cost
  ->   from Actuals a,
  ->   (select distinct Category from Budget) t1
  ->   where a.Task IN (Select Task from Budget where Category = t1.Category)
  ->   group by t1.Category
  -> ) x,
  -> (
  ->   select Category, sum(Estimated_Cost) Estimated_Cost
  ->   from Budget
  ->   group by Category
  -> ) y
  -> where x.Category = y.Category;
+----------+----------------+-------------+
| Category | Estimated_Cost | Actual_Cost |
+----------+----------------+-------------+
|     9100 |         121.00 |       77.00 |
|     9200 |          19.00 |       15.00 |
+----------+----------------+-------------+

Upvotes: 0

mindaJalaj
mindaJalaj

Reputation: 448

You can do a left join on table 1 and table 2 on task column, and with help of group by clause, calculate the sum of cost based on categories. Below is the query

Just single query will help you achieve the goal. 

select Category,sum(distinct(Estimated_Cost)),sum(Actual_Cost)
from Table1 t1 left join Table2 t2
on t1.task = t2.task
groupd by t1.Category

Just wondering even if task is not part of table 2, should we still show sum in estimated cost?

Hope this helps, thanks.

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

One possible solution is to use two group by sql commands and then join them according to the category

select te.category, sum_estimated_cost, sum_actual_cost
from (
    select 
    select category,
           sum(Estimated_Cost) sum_estimated_cost
    from Table1 t1
    group by t1.category 
) te
join
(
    select category, 
           sum(Actual_Cost) sum_actual_cost
    from Table1 t1, Table2 t1
    where t1.task = t2.task
    groupy by t1.category 
) ta on te.category = ta.category

Upvotes: 0

Related Questions