princevezt princevezt - 5 months ago 30
SQL Question

SQL query to JOIN two tables with no relationship

I am having trouble getting the records on the Monthly average Cost per Item with this table values.
enter image description here

enter image description here

I need a result like this:

enter image description here

What I want to get is all the month from January to December even if there is no transaction on that month of that item.

Is this possible ?


Here you go (assuming T-SQL):

select b.ItemName, a.MonthName, round(coalesce(avg(c.Cost), 0)) as avg_cost
from table_1 a
join (select ItemName from table_2 group by ItemName) b
on 1 = 1
left join table_2 c
on month(c.TransactionDate) = a.MonthID
and b.ItemName = c.ItemName
group by b.ItemName, a.MonthName
order by b.ItemName, a.MonthID;