Getting SUM and JOIN to cooperate in a specific query

Question

I'm stuck with a certain query, it seems to be simple but it seems I might have to split it in different ways to get it to accomplish what I want to do. I tried combining, recombining and I could use some guidance.

Here's a sample table, the output and expected output.

Shop_Table

shop_id |     item_type  | date

   1          soaps         2000-01-01
   2          food          2000-01-02
   3          appliances    2000-01-03
   4          electronics   2000-01-10
   5          furniture     2000-01-13
   6          misc.         2000-01-15

Instance_Table

instanceid|    shop_id    | firstname | lastname | number_of_items

   11            3            jane        doe          2
   22            2            jane        doe          3
   33            1            jane        doe          5
   44            4            jane        doe          6
   55            6            jane        doe          1
   66            5            jane        doe          2

The Query

SELECT 
IF
( 
    Shop_Table.item_type
    IN
        (
            'soaps',
            'food'
         ),
    'indexpensives',
    Shop_Table.item_type
), 
COALESCE(SUM(Person.number_of_items), 0) AS item_sum
FROM Shop_Table

INNER JOIN Instance_Table AS Instance
ON Instance.shop_id = Shop_Table.shop_id
AND Instance.firstname =  'jane'
AND Instance.lastname =  'doe'
AND Shop_Table.date BETWEEN DATE(  '2000-01-01' ) AND DATE(  '2000-01-03' )

GROUP BY Shop_Table.item_type

Which returns:

  item_type     |     item_sum

indexpensives   |        8
appliances      |        2

Output

Which is great so far, and is where I'm stuck. What I really want is the following output:

Expected Output

  item_type     |     item_sum

inexpensives    |        8
appliances      |        2
electronics     |        0
furniture       |        0
misc.           |        0

I think it's pretty straight forward what I want to do. What's making it a little tough is that it has a little bit of everything.

• Grouping and combining sum results (soaps and food into one result)
• Returning 0 for the rest of the item types when the date is outside the specified range.

Update:

Turned out a little more complicated than I thought, but it's what I wanted. Thank you everyone for helping out.

SELECT 
IF
( 
    Shop_Table.item_type
    IN
        (
            'soaps',
            'food'
         ),
    'inexpensives',
    Shop_Table.item_type
), 
COALESCE(SUM(Person.number_of_items), 0) AS item_sum
FROM Shop_Table
LEFT OUTER JOIN Instance_Table AS Instance
ON Instance.shop_id = Shop_Table.shop_id
AND Instance.firstname =  'jane'
AND Instance.lastname =  'doe'
AND Shop_Table.date BETWEEN DATE(  '2000-01-01' ) AND DATE(  '2000-01-03' )
GROUP BY IF
( 
    Shop_Table.item_type
    IN
        (
            'soaps',
            'food'
         ),
    'inexpensives',
    Shop_Table.item_type
)

Answer 1

First of all replace your inner join with a left join and then make sure you coalesce the sum with a 0, like this:

SELECT 
IF
( 
    Shop_Table.item_type
    IN
        (
            'soaps',
            'food'
         ),
    'indexpensives',
    Shop_Table.item_type
), 
COALESCE(SUM(Person.number_of_items), 0) AS item_sum
FROM Shop_Table
LEFT JOIN Instance_Table AS Instance
ON Instance.shop_id = Shop_Table.shop_id
AND Instance.firstname =  'jane'
AND Instance.lastname =  'doe'
AND Shop_Table.date BETWEEN DATE(  '2000-01-01' ) AND DATE(  '2000-01-03' )
GROUP BY Shop_Table.item_type

Edit:

After the requirement changes, this should be the final query:

SELECT 
IF
( 
    Shop_Table.item_type
    IN
        (
            'soaps',
            'food'
         ),
    'inexpensives',
    Shop_Table.item_type
) as FinalType, 
COALESCE(SUM(Person.number_of_items), 0) AS item_sum
FROM Shop_Table
LEFT OUTER JOIN Instance_Table AS Instance
ON Instance.shop_id = Shop_Table.shop_id
AND Instance.firstname =  'jane'
AND Instance.lastname =  'doe'
AND Shop_Table.date BETWEEN DATE(  '2000-01-01' ) AND DATE(  '2000-01-03' )
GROUP BY FinalType

Answer 2

Change your query to this and you should get the totals you're looking for:

SELECT 
IF
( 
    Shop_Table.item_type
    IN
        (
            'soaps',
            'food'
         ),
    'indexpensives',
    Shop_Table.item_type
), 
SUM(Person.number_of_items) AS item_sum
FROM Shop_Table

LEFT OUTER JOIN Instance_Table AS Instance
ON Instance.shop_id = Shop_Table.shop_id
AND Instance.firstname =  'jane'
AND Instance.lastname =  'doe'
AND Shop_Table.date BETWEEN DATE(  '2000-01-01' ) AND DATE(  '2000-01-03' )

GROUP BY Shop_Table.item_type

Explanation
An outer join will select all rows from one table and matching rows from another. So, a LEFT OUTER JOIN will select all rows from the table on the left side of the join (Shop_Table in this case) and matching rows from the table on the right side of the join (Instance_Table in this case).