SQL multiply row times value from another table

Question

I am trying to write a query to tell me the cost of all my projects in a SQL table. I have two tables on contains my project along with number of hours necessary for different types of work and the year it is being done.

The other table tells me for what year and type of work being done what the cost per hour will be.

project_table:

project_id | year | H1 | H2 | H3
-----------+------+----+----+---
001        | 2017 | 4  | 2  | 0
002        | 2016 | 3  | 5  | 3
003        | 2018 | 6  | 0  | 6
004        | 2018 | 0  | 1  | 9

cost_table:

Year | hour_type | rate
-----+-----------+------
2016 | h1        | 2
2016 | h2        | 2
2016 | h3        | 1
2017 | h1        | 5
2017 | h2        | 1
2017 | h3        | 2
2018 | h1        | 4
2018 | h2        | 3
2018 | h3        | 6

Given these tables the the cost for project 001 would be

(4 * 5) + (2 * 2) + (0 * 2) = 24 cost

Is there a query that will give me this for every project?

I would like a table that looks like this

project | cost
--------+-------
001     | 24
002     |...
...

Answer 1

I turned your sample data into a table so it is super easy to work with.

create table project_table
(
    project_id varchar(10)
    , ProjectYear int
    , H1 int
    , H2 int
    , H3 int
)
insert project_table values
(001, 2017, 4, 2, 0)
, (002, 2016, 3, 5, 3)
, (003, 2018, 6, 0, 6)
, (004, 2018, 0, 1, 9)

create table cost_table
(
    ProjectYear int
    , hour_type char(2)
    , rate int
)

insert cost_table values
(2016, 'h1', 2)
, (2016, 'h2', 2)
, (2016, 'h3', 1)
, (2017, 'h1', 5)
, (2017, 'h2', 1)
, (2017, 'h3', 2)
, (2018, 'h1', 4)
, (2018, 'h2', 3)
, (2018, 'h3', 6)

Now that we have sample data you can solve this quite easily using conditional aggregation. I included the value for each rate type as well as column with the value you want so you can see the individual values.

select p.project_id
    , max(case when c.hour_type = 'h1' then p.H1 * c.rate end) as H1
    , max(case when c.hour_type = 'h2' then p.H2 * c.rate end) as H2
    , max(case when c.hour_type = 'h3' then p.H3 * c.rate end) as H3
    , MyTotalCost = max(case when c.hour_type = 'h1' then p.H1 * c.rate end) +  max(case when c.hour_type = 'h2' then p.H2 * c.rate end) + max(case when c.hour_type = 'h3' then p.H3 * c.rate end) 
from project_table p
join cost_table c on c.ProjectYear = p.ProjectYear
group by p.project_id