Extract Specific Data After a aggregation (Or any other solution for the desired result)

Question

I want to select the Total "sales" of a specific "main_category" for the year 2016

(main categories that don't have sales in that year should appear as zero)

I have managed to select the "sales" of a specific "main category" with all the other "main_categories" (that doesn't have any sales) appearing as zero using below query:

SELECT 
    mc.name,
    ISNULL(SUM(s.no_of_units * b.unit_price),0) AS tCatSales
FROM Sales s
INNER JOIN Invoice i ON i.invoice_ID = s.invoice_id
INNER JOIN Inventory inv ON inv.inventory_ID = s.inventory_ID
INNER JOIN Batch b ON b.batch_ID = inv.batch_ID
INNER JOIN Products p ON p.product_id = b.product_ID
INNER JOIN Category c ON c.category_ID = p.category_id
RIGHT JOIN Main_Category mc ON mc.cat_id = c.main_category
--WHERE YEAR(i.trans_date) = 2016
GROUP BY mc.name
--HAVING YEAR(i.trans_date)=2016

but when I try to further segregate it for year 2016 ONLY either by WHERE clause or HAVING clause, it stops showing "main_category" names that have zero sales in the year.

One thing that I can think of is to give the query invoices only from 2016 which I tried to did by doing something like,

Replacing the line: INNER JOIN Invoice i ON i.invoice_ID = s.invoice_id

with: INNER JOIN Invoice i ON i.invoice_ID IN (SELECT invoice_id FROM Invoice in2 WHERE Year(in2.trans_date)=2016)

which did display the categories with zero values but with increased the calculated Sales Amount (from 2069 to something 203151022.75).

I understand this addition is somewhat illogical and disrupts the whole Inner Joins but so far these are the closest thing I can think of or find on the web.

I REPEAT the desired result is: main categories that don't have sales in that year should appear as zero with the year given year/month/date

Answer 1

As Sean and Eli mentioned, RIGHT JOIN is not recommended, you may change it to LEFT JOIN, OR use subquery like this:

SELECT
    mc.name,
    tCatSales = ISNULL(
      (
        SELECT 
            SUM(s.no_of_units * b.unit_price) AS tCatSales
        FROM Sales s
        INNER JOIN Invoice i ON i.invoice_ID = s.invoice_id
        INNER JOIN Inventory inv ON inv.inventory_ID = s.inventory_ID
        INNER JOIN Batch b ON b.batch_ID = inv.batch_ID
        INNER JOIN Products p ON p.product_id = b.product_ID
        INNER JOIN Category c ON c.category_ID = p.category_id    
        WHERE mc.cat_id = c.main_category  
          AND YEAR(i.trans_date) = 2016
      ) , 0)
FROM Main_Category mc