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

Just posting a query and expecting other to solve it is not likely going to happen. We need more information to offer any real help here. This is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ If you keep your where clause the way you have it your entire query logic changes to return only those rows with a match. I would start your entire query over and use Main_Category as your base table. Then left join all the other stuff. Right joins are confusing for even experienced people, they are downright flustering for other. — Sean Lange, Jan 13 '17 at 17:16

score 0 · Answer 1 · answered Sep 06 '17 at 20:10

try this:

WHERE ISNULL(YEAR(i.trans_date), 1) = 2016

if you put simple equals conditions on outer join it will eliminate nulls, which give zero-valued rows you desire. Also note that something like:

WHERE YEAR(i.trans_date) = 2016

is not sargable, see here

score 0 · Accepted Answer · answered Sep 06 '17 at 20:43

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

Your suggestion of making a sub-query somewhat helped me in my solution. — Daniyal Nasir, Nov 06 '17 at 13:02

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

2 Answers2