CODEWITHSUNDEEP
sqlsql-serverselectgroup-bysum
Marl Xtian Monzon Marcera
Marl Xtian Monzon Marcera

Reputation: 85

Query to output an item not in the main table and give it a value stating it isn't existing

So I have a table that looks like this:

item_name | Cost
Item1     | 14
Item2     | 20
Item2     | 50
Item3     | 75
Item3     | 15

And a query that looks like this:

select item_name, sum(cost) from item_costs where item_name IN ('Item1','Item2','Item3','Item4') GROUP BY item_name

What it would normally output would be the item's name and the sum of their costs. Which looks like this:

item_name | Cost
Item1     | 14
Item2     | 70
Item3     | 90

What we want it to output is all the items from the query. It could output '-1' or '0' if the item is not in the main table, in this case Item4. How should we modify our query to do that?

Upvotes: 0

Views: 288

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

You need to use a left join; I would suggest with values():

select v.item_name, coalesce(sum(ic.cost), 0) as cost
from (values ('Item1'), ('Item2'), ('Item3'), ('Item4')) v(item_name) left join
     item_costs ic
     on ic.item_name = i.item_name
group by v.item_name;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use values() construct & do left join :

select t.item_name, isnull(sum(tc.Cost), 0) as Cost
from ( values ('Item1'), . . , ('Item4')
     ) t(item_name) left join
     item_costs ic
     on ic.item_name = t.item_name
group by t.item_name;

Upvotes: 0

Related Questions