Summing Sales Data

  • 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
  • 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

  • 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

  • Thanks for the responses guys.

    Just what I needed.

    BO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply