Извлечение определенных данных после агрегации (или любого другого решения для получения желаемого результата)

Я хочу выбрать общий объем «продаж» конкретной «main_category» за 2016 год

(основные категории, которые не имеют продаж в этом году должны отображаться как ноль)

Мне удалось выбрать » продажи «определенной» главной категории «со всеми другими» main_categories » (которые не имеют никаких продаж), появляющихся как ноль, используя запрос ниже:

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

но когда я пытаюсь далее разделить его на 2016 год только или предложением WHERE, или предложением HAVING, он перестает показывать имена «main_category», у которых нулевые продажи в году.

Одна вещь, которую я могу думать о том, чтобы дать запрос счетов-фактур только с 2016 года
что я пытался сделать, делая что-то вроде,

Замена линии:
INNER JOIN Invoice i ON i.invoice_ID = s.invoice_id

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

который действительно отображал категории с нулевыми значениями, но с увеличением расчетной суммы продаж (с 2069 до чего-то 203151022.75) .

Я понимаю, что это дополнение несколько нелогично и нарушает все внутренние соединения, но пока это самое близкое, что я могу придумать или найти в интернете.

Я повторяю, что желаемый результат: основные категории, у которых нет продаж в этом году, должны отображаться как ноль с годом, заданным годом / месяцем / датой

2 ответа

  1. попробовать это:

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

    если вы поставите простые условия равенства на внешнее соединение, это устранит нули,которые дают строки с нулевым значением, которые вы хотите.
    Также обратите внимание, что что-то вроде:

    WHERE YEAR(i.trans_date) = 2016
    

    не sargable, см. здесь

  2. Как упоминали Шон и Эли, правое соединение не рекомендуется, вы можете изменить его на левое соединение или использовать вложенный запрос, как это:

    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