MySQL-求和结果并加入单个查询

Question

表1中各个程序的总小时数应与表2中的prod_count相乘。

例如：让我们拿一个用户ram

ram已经完成了两个程序pro-1 = 9hr (5 + 4)和pro-2 = 6 hr[来自表1]

pro-1 is 200和pro-2 is 120的prod_count[来自表2]

所以理想情况下应该是200*9 = 1800和120*6 = 720

所以total is (1800 + 720) = 2520

total task count is (656 + 23 + 44) = 723[来自表1]

ram的结果应为ram, 2520 , 723

表1

| id | name  | program | task_hours | task_count |
|----|-------|---------|------------|------------|
| 1  | raj   | pro-1   | 6.5        | 344        |
| 2  | raj   | pro-2   | 2          | 324        |
| 3  | ram   | pro-1   | 5          | 656        |
| 4  | ram   | pro-1   | 4          | 23         |
| 5  | ram   | pro-2   | 6          | 44         |
| 6  | kumar | pro-2   | 7          | 345        |
| 7  | kumar | pro-3   | 4          | 222        |

表2

| id | program | prod_count |
|----|---------|------------|
| 1  | pro-1   | 200        |
| 2  | pro-2   | 120        |
| 3  | pro-3   | 60         |

预期结果：

name  | total_calc | total_count |
|-------|------------|-------------|
| raj   | 1540       | 668         |
| ram   | 2520       | 723         |
| kumar | 1080       | 567         |

SQL Fiddle-http://sqlfiddle.com/#！9/f75e15

Answer 1

加入表并聚合：

select t1.name,
sum(t1.task_hours * t2.prod_count) total_calc,
sum(t1.task_count) total_count
from Table1 t1 inner join Table2 t2
on t2.program = t1.program
group by t1.name

请参阅演示
结果：

| name  | total_calc | total_count |
| ----- | ---------- | ----------- |
| kumar | 1080       | 567         |
| raj   | 1540       | 668         |
| ram   | 2520       | 723         |

Answer 2

试试这个：http://sqlfiddle.com/#！9/eb5f61/1/0

select
name,
sum(task_hours * prod_count) total_calc,
sum(task_count) total_count
from table1 t1
join table2 t2
on t1.program = t2.program
group by name
order by t1.id

注意：task_hours应该是float类型。