CODEWITHSUNDEEP
sqlsql-server
Megi Ben Nun
Megi Ben Nun

Reputation: 415

MIN function doesn't work correctly in a SQL complicated query

The task is to select the sub category with the minimum profit per year. The next query, selects few sub categories per year:

select 
  min (Profit), 
  CalendarYear, 
  EnglishProductSubcategoryName
from (
  select 
    SUM(fis.SalesAmount-fis.TotalProductCost) Profit, 
    t.CalendarYear, 
    sc.EnglishProductSubCategoryName
    from FactInternetSales fis 
    inner join DimProduct p 
            on fis.ProductKey = p.ProductKey
    inner join DimProductSubcategory sc 
            on p.ProductSubcategoryKey = sc.ProductSubcategoryKey
    inner join DimTime t 
            on fis.DueDateKey = t.TimeKey
    group by CalendarYear, EnglishProductSubcategoryName) aa 
    --Order by CalendarYear
) aa
group by CalendarYear, EnglishProductSubcategoryName
order by CalendarYear

Upvotes: 2

Views: 1377

Answers (1)

marc_s
marc_s

Reputation: 754508

If you want to find the category with the minimum profit for a given year, you need to rewrite your query:

select 
    Profit,
    CalendarYear, 
    EnglishProductSubcategoryName
from 
    (..... ) aa
where
    CalendarYear = 2011
    AND Profit = (SELECT MIN(Profit) FROM aa WHERE aa.CalendarYear = 2011)

This will find the row(s) - it could be multiple - that have the minimum profit (for 2011) as reported back by the subquery.

Update: since you need the minimum profit for every year, I would probably totally rewrite this query to something like:

;WITH YearlyProfitsByCategory AS
(
   SELECT
      SUM(fis.SalesAmount - fis.TotalProductCost) Profit, 
      t.CalendarYear, 
      sc.EnglishProductSubCategoryName
   FROM
      dbo.FactInternetSales fis 
   INNER JOIN
      dbo.DimProduct p ON fis.ProductKey = p.ProductKey
   INNER JOIN
      dbo.DimProductSubcategory sc ON p.ProductSubcategoryKey = sc.ProductSubcategoryKey
   INNER JOIN
      dbo.DimTime t ON fis.DueDateKey = t.TimeKey
   GROUP BY
      t.CalendarYear, 
      sc.EnglishProductSubCategoryName
),
YearlyMinProfits AS 
(
    SELECT
       CalendarYear, 
       EnglishProductSubCategoryName,
       Profit,
       RowNum = ROW_NUMBER() OVER (PARTITION BY CalendarYear ORDER BY Profit)
    FROM YearlyProfitsByCategory 
)
SELECT 
   CalendarYear, EnglishProductSubCategoryName, Profit
FROM YearlyMinProfits
WHERE RowNum = 1  -- the row with the smallest profit, for every year

This uses a CTE (Common Table Expression) and the ROW_NUMBER() ranking function - both available in SQL Server 2005 and newer (you didn't mention your version in your question)

Upvotes: 1

Related Questions