Summing Sales Data

• ByronOne

  SSCarpal Tunnel

  Points: 4656

  More actions

  February 8, 2018 at 2:20 pm

  #327406

  Hi
  If I run the script below I get the following output.

  [Store] [Planned Sales] [Planned Sales Year] [Actual Sales]
  South - 50 - 50 - 22 

  How can I adapt my select statement so my [Planned Sales Year] = 120 (which is correct) and not 50. Removing the SaleMonth clause would result in my [Planned Sales] also increasing to 120 which I don't want as I want to see the year to date position for planned and actual sales.

  Thanks 
  BO

  create table #Sales
  (
  SaleMonth int null,
  Store varchar(25) null,
  PlannedSales float null,
  PlannedSalesYear float null,
  ActualSales float null)

  insert into #Sales (SaleMonth, Store, PlannedSales,PlannedSalesYear, ActualSales)
  values (1, 'South', 10, 10, 5),
         (2, 'South', 10, 10, 3),
      (3, 'South', 10, 10, 4),
      (4, 'South', 10, 10, 2),
      (5, 'South', 10, 10, 8),
      (6, 'South', 10, 10, null),
      (7, 'South', 10, 10, null),
      (8, 'South', 10, 10, null),
      (9, 'South', 10, 10, null),
      (10, 'South', 10, 10, null),
      (11, 'South', 10, 10, null),
      (12, 'South', 10, 10, null);

  select 
         Store,
      sum(PlannedSales) as 'Planned Sales',
      sum(PlannedSalesYear) as 'Planned Sales Year',
      sum(ActualSales) as 'Actual Sales'
  from #Sales
  where SaleMonth <= 5
  group by Store
     

  drop table #Sales

• drew.allen

  SSC Guru

  Points: 76997

  More actions

  February 8, 2018 at 3:14 pm

  #1979120

  Use a CASE expression instead of a WHERE clause.

  select
Store,
sum(CASE WHEN SaleMonth <= 5 THEN PlannedSales END) as 'Planned Sales',
sum(PlannedSalesYear) as 'Planned Sales Year',
sum(ActualSales) as 'Actual Sales'
from #Sales
group by Store
drop table #Sales

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• george_at_sql

  SSCommitted

  Points: 1967

  More actions

  February 8, 2018 at 10:00 pm

  #1979168

  Including the case statement for the "Actual Sales" column would ensure you get the sales for the first 5 months.
  Currently the "ActualSales" for the rest of the months is null for SaleMonth>5 so it doesnt matter

  
  select
  Store,
  sum(CASE WHEN SaleMonth <= 5 THEN PlannedSales END) as 'Planned Sales',
  sum(PlannedSalesYear) as 'Planned Sales Year',
  sum(CASE WHEN SaleMonth <= 5 THEN ActualSales END) as 'Actual Sales' /*Added the statement from Drews Solution*/
  from #Sales
  group by Store

• ByronOne

  SSCarpal Tunnel

  Points: 4656

  More actions

  February 12, 2018 at 2:54 pm

  #1979531

  Thanks for the responses guys.

  Just what I needed.

  BO