CODEWITHSUNDEEP
sqlsql-server
M.tovey
M.tovey

Reputation: 31

Sum within a Case statement

I'm trying to calculate a field when a condition is met. i've been playing about with this for hours but have had no luck :(.

So I need to find and calculate the Margin for each month. i did this for sales which was a simple

SUM(CASE when CalendarMonth = 1 then TotalSales else 0 end) as 'January',

However when trying to calculate the margin i often get the error 'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.'

Am i going about this the complete wrong way?

    Declare @Sales numeric, @Cost numeric, @Margin Numeric

    Set @Sales = (Select SUM(TotalSales)
            From SalesAnalysis2)

    Set @Cost = (Select SUM(TotalCost)
            From SalesAnalysis2)

    Set @Margin = ((@Sales - @Cost) / @Sales)

    Select distinct

    Case CalendarMonth when 1 then @Margin else 0 end as 'January',
    Case CalendarMonth when 2 then @Margin else 0 end as 'Febuary',
    Case CalendarMonth when 3 then @Margin else 0 end as 'March',
    Case CalendarMonth when 4 then @Margin else 0 end as 'April',
    Case CalendarMonth when 5 then @Margin else 0 end as 'May',
    Case CalendarMonth when 6 then @Margin else 0 end as 'June',
    Case CalendarMonth when 7 then @Margin else 0 end as 'July',
    Case CalendarMonth when 8 then @Margin else 0 end as 'August',
    Case CalendarMonth when 9 then @Margin else 0 end as 'September',
    Case CalendarMonth when 10 then @Margin else 0 end as 'Octocber',
    Case CalendarMonth when 11 then @Margin else 0 end as 'November',
    Case CalendarMonth when 12 then @Margin else 0 end as 'December'

    From SalesAnalysis2

    where CustomerID = 2829 and BranchID = 0 and CalendarYear = Year(Getdate())

The code above gives this result but only 0s

This is the sales one i did, i need to use the same format to display margin. However there isn't a margin field within the same table, it needs to be calculated Sales Example

Upvotes: 2

Views: 1263

Answers (3)

Pankaj Kumar
Pankaj Kumar

Reputation: 570

Please try:

Declare @Sales numeric, @Cost numeric, @Margin Numeric

Set @Sales = (Select SUM(TotalSales)
        From SalesAnalysis2)

Set @Cost = (Select SUM(TotalCost)
        From SalesAnalysis2)

Set @Margin = ((@Sales - @Cost) / @Sales)

Select distinct

Case Month(YourDateColumn) =1 then @Margin else 0 end as 'January',
Case Month(YourDateColumn) =2 then @Margin else 0 end as 'Febuary',
Case Month(YourDateColumn) =3 then @Margin else 0 end as 'March',
Case Month(YourDateColumn) =4 then @Margin else 0 end as 'April',
Case Month(YourDateColumn) =5 then @Margin else 0 end as 'May',
Case Month(YourDateColumn) =6 then @Margin else 0 end as 'June',
Case Month(YourDateColumn) =7 then @Margin else 0 end as 'July',
Case Month(YourDateColumn) =8 then @Margin else 0 end as 'August',
Case Month(YourDateColumn) =9 then @Margin else 0 end as 'September',
Case Month(YourDateColumn) =10 then @Margin else 0 end as 'Octocber',
Case Month(YourDateColumn) =11 then @Margin else 0 end as 'November',
Case Month(YourDateColumn) =12 then @Margin else 0 end as 'December'

From SalesAnalysis2

where CustomerID = 2829 and BranchID = 0 and CalendarYear = Year(Getdate())

Upvotes: 0

Wes H
Wes H

Reputation: 4439

You need to perform your margin calculation for each month, rather than using the variable that you've only set once. In addition, to use the case statement as a pivot you'll also need a MAX aggregate to reduce the number of rows returned. Otherwise you'll get a row for each month, with only one column in each row having a value other than 0.

WITH p AS
  (
    SELECT      CalendarMonth,
                TotalSales = SUM( TotalSales ),
                TotalCost = SUM( TotalCost ),
                Profit = SUM( TotalSales - TotalCost )
      FROM      SalesAnalysis2
      GROUP BY  CalendarMonth
  )
  SELECT
    MAX(CASE CalendarMonth when 1 then p.Profit/p.TotalSales else 0 END) as 'January',
    MAX(Case CalendarMonth when 2 then p.Profit/p.TotalSales else 0 END) as 'Febuary',
    MAX(Case CalendarMonth when 3 then p.Profit/p.TotalSales else 0 END) as 'March',
    MAX(Case CalendarMonth when 4 then p.Profit/p.TotalSales else 0 END) as 'April',
    MAX(Case CalendarMonth when 5 then p.Profit/p.TotalSales else 0 END) as 'May',
    MAX(Case CalendarMonth when 6 then p.Profit/p.TotalSales else 0 END) as 'June',
    MAX(Case CalendarMonth when 7 then p.Profit/p.TotalSales else 0 END) as 'July',
    MAX(Case CalendarMonth when 8 then p.Profit/p.TotalSales else 0 END) as 'August',
    MAX(Case CalendarMonth when 9 then p.Profit/p.TotalSales else 0 END) as 'September',
    MAX(Case CalendarMonth when 10 then p.Profit/p.TotalSales else 0 END) as 'Octocber',
    MAX(Case CalendarMonth when 11 then p.Profit/p.TotalSales else 0 END) as 'November',
    MAX(Case CalendarMonth when 12 then p.Profit/p.TotalSales else 0 END) as 'December'
  FROM    p

Since you have not provided any schema or data, you will need to modify this to fit your data. It may not provide accurate results for your data as written, but it should at least return different values for each month and will hopefully get you closer to the solution you need.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You want a sum():

Select sum(Case CalendarMonth when 1 then @Margin else 0 end) as January,
       sum(Case CalendarMonth when 2 then @Margin else 0 end) as February,
       . . .
From SalesAnalysis2
where CustomerID = 2829 and BranchID = 0 and
      CalendarYear = Year(Getdate())

Upvotes: 0

Related Questions