locked
Conditions in window function RRS feed

  • Question

  • Hi, I am using SQL Server 2008 R2 we have sale information table which has sales information made by every sales representative and prior to the year 2007 most of this information was hand entered and I am trying to get a count of all the sales for each week and all the sales count that has for instance Unit sold. How would I write this?

    Thanks in advance.........

    SELECT 
    COUNT(SaleId) OVER (PARTITION BY [Year], [Week], City) AS AllSales
    -- For instance 17 all sales count 
    COUNT(SaleId) OVER (PARTITION BY [Year], [Week], City) 
    AS SaleswithsoldUnits
    --Where Units is not a null value sales count 14
    FROM dbo.Sales
    GO

    How to re-write SaleswithsoldUnits field?
    Tuesday, February 26, 2013 7:05 PM

Answers

  • SELECT 
    COUNT(SaleId) OVER (PARTITION BY [Year], [Week], City) AS AllSales,
    -- For instance 17 all sales count 
    COUNT(case when Units IS NOT NULL then SaleId END) OVER (PARTITION BY [Year], [Week], City) 
    AS SaleswithsoldUnits
    --Where Units is not a null value sales count 14
    FROM dbo.Sales
    GO


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Iric Wen Thursday, March 7, 2013 8:48 AM
    Tuesday, February 26, 2013 7:09 PM
  • I believe we don't have DISTINCT in window functions, so we would need to do a separate GROUP BY query, e.g.

    ;with cteSummary as (select [Year], [Week], [City],

    count(SaleID) as AllSales,

    count(distinct (case when Units IS NOT NULL then SaleID end)) as [SalesWithSoldUnits]

    from dbo.Sales

    GROUP BY [Year],[Week],[City])

    -- Now you can join with the summary from the main query.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Iric Wen Thursday, March 7, 2013 8:48 AM
    Tuesday, February 26, 2013 9:04 PM

All replies

  • SELECT 
    COUNT(SaleId) OVER (PARTITION BY [Year], [Week], City) AS AllSales,
    -- For instance 17 all sales count 
    COUNT(case when Units IS NOT NULL then SaleId END) OVER (PARTITION BY [Year], [Week], City) 
    AS SaleswithsoldUnits
    --Where Units is not a null value sales count 14
    FROM dbo.Sales
    GO


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Iric Wen Thursday, March 7, 2013 8:48 AM
    Tuesday, February 26, 2013 7:09 PM
  • Thanks Naomi. How do we modify this query to get distinct SaleId's?

    Regards.....

    Tuesday, February 26, 2013 8:56 PM
  • I believe we don't have DISTINCT in window functions, so we would need to do a separate GROUP BY query, e.g.

    ;with cteSummary as (select [Year], [Week], [City],

    count(SaleID) as AllSales,

    count(distinct (case when Units IS NOT NULL then SaleID end)) as [SalesWithSoldUnits]

    from dbo.Sales

    GROUP BY [Year],[Week],[City])

    -- Now you can join with the summary from the main query.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Iric Wen Thursday, March 7, 2013 8:48 AM
    Tuesday, February 26, 2013 9:04 PM
  • I would build a calendar table with aISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyyis the year, W is as separator token, wwis (01-53) week number and d is (1-7) day of the week.

    You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate. There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, February 27, 2013 1:52 AM