STDEV() on a count() with Grouping?

  • Can someone tell me if this is possible?

    i have a set of customers trading different categories of items across several months.

    i want to count the number of times they trade, by customer, by category, by month.

    this part is easy and i have loaded the set into a temp table #T1.

    i then want to know the STDEV of the trade activity ie how active they are grouped by month - so a customer may trade shoes 5 times in may, 1 time in june and 15 times in july etc.

    i do not want the overall STDEV of the trade_count column because thats the entire population of customers. i need it grouped by customer.

    i was hoping that:

    SELECT STDEV(Trade_Count) AS Trade_Count_StDev, * FROM #T2

    GROUP BY Customer, [CategoryType], DealMonth,

    for the new stdev coluimn i get all NULLS back.

    if i take the GROUP BY off i get a single numerical STDEV.

    SELECT STDEV(Trade_Count) AS Trade_Count_StDev FROM #T2

  • Do you mean to take the standard deviation of the monthly totals by customer, category, etc? If so, try using a cte or subquery to develop those totals with a summary query, then apply stddev to the resulting derived table.

    Also, could you post up some sample data? I'm puzzled about why you are getting nulls with the query you're showing.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Actually, the query that you poseted was syntactically incomplete.

    What does this query return?

    SELECT Customer, [CategoryType], DealMonth

    , STDEV(Trade_Count) AS Trade_Count_Stdev

    , COUNT(Trade_Count) AS Trade_Count_Records FROM #T2

    GROUP BY Customer, [CategoryType], DealMonth

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Barry - this suggests to me that i need an additional aggregate in the query with the count of the trade count.

    in your example i get some stdev's back but we are stdev calcing on a count of the tradecount ie a count of a count. this is therefore not the true stdev of the underlying data.

    interestingly i get some Nulls and this happens when a customer trades the same amount on > 1 month and i get zero if they trade only once or multiple times in one month.

    sql2005 version: Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86) Aug 5 2008 01:01:05 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    probably best to generate some code to help

    =====================================

    dealid customer product category tradeMonthYear

    10126 my co ltd myproduct leathergoods Oct-08

    96127 my co ltd myproduct leathergoods Oct-08

    62088 my co ltd myproduct leathergoods Nov-08

    34814 my co ltd myproduct leathergoods Nov-08

    51796 my co ltd myproduct leathergoods Feb-08

    10517 my co ltd myproduct leathergoods Jan-08

    25950 my co ltd myproduct leathergoods Jan-08

    21692 my co ltd myproduct leathergoods Jan-08

    55644 my co ltd myproduct leathergoods Jul-08

    55640 my co ltd myproduct leathergoods Jul-08

    00708 my co ltd myproduct leathergoods Sep-08

    54361 my co ltd myproduct leathergoods Sep-08

    60690 my co ltd myproduct leathergoods Sep-08

    86866 my co ltd myproduct leathergoods Mar-08

    39081 my co ltd myproduct leathergoods Apr-08

    68492 my co ltd myproduct leathergoods Feb-08

    96775 my co ltd myproduct leathergoods Mar-08

    76525 my co ltd myproduct leathergoods Mar-08

    99765 my co ltd myproduct leathergoods Mar-08

    DealId has to be a varchar.

    sql to group the trades

    ===================

    select

    COUNT(DealId)as Trade_Count,

    Customer,

    Product,

    Category,

    tradeMonthYear

    from tbl_Sales

    GROUP BY Customer, [Product], Category, tradeMonthYear

    i definately need these group by's because i have many customers trading many products and cannot use an overall stdev.

    refine this by adding stdev:???

    ========================

    select

    COUNT(DealNo)as Trade_Count,

    STDEV(COUNT(DealNo)) as StdevCalc,

    Customer,

    Product,

    Category,

    tradeMonthYear

    from tbl_Sales

    GROUP BY Customer, [Product], Category, tradeMonthYear

    ======================================

    error: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

    "

    ======================================

    the error is clear enough and doesnt like the count function inside the stdev function.

    to get around this a dumped the first query into a #T1 and used a #T2 with a new column for the stddev calc.

    ============

    SELECT STDEV(Trade_Count) AS Trade_Count_StDev, Trade_Count,

    ============

    this way i didnt have to pass a count() into the stdev. this didnt work either.

  • Well, I am pretty lost as to exactly what you are trying to do. Please read this article and then provide the information that it recommends: http://www.sqlservercentral.com/articles/Best+Practices/61537/

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • this is now resolved. having thought through the problem i made one adjustment to get the desired result.

    =================================

    aIM: To get the Stdev of trading activity by customer , product and category across several months of trades.

    temp tbl #1 group the trades by count of unique deal number

    ===============================================

    select

    COUNT(DealId)as Trade_Count,

    Customer,

    Product,

    Category,

    tradeMonthYear

    from tbl_Sales

    GROUP BY Customer, [Product], Category, tradeMonthYear

    create temp tbl #2 : insert contents of #1

    ================================

    INSERT INTO #T2 SELECT * FROM #T1

    calc the std dev of trade activity by customer,prod,category:

    =============================================

    SELECT

    ISNULL(STDEV(Trade_Count),0.00) AS Trade_Count_StDev, Customer, Product, Category

    FROM #T2

    GROUP BY Customer, Product, Category

    DO NOT INCLUDE THE tradeMonthYear IN THIS !!!

    This then produces the exact same result as excel would through the stdev() function.

    I f you do include tradeMonthYear (a varchar(128) )in the select and groupby you get a column of ZEROs for the stdev.

    the tradeMonthYear field is a concat of month and year ie January08

    tbl #1 counts the number of trades and groups by tradeMonthYear.

    in tbl #2, you therefore never have a repeat tradeMonthYear for a particular product sold due to the grouping.

    it therefore does not make sense to include a group by tradeMonthYear in the stdev calc because there is no deviation on a unique row.

    the stdev must be applied against the unique products across all the months of trading.

Viewing 6 posts - 1 through 6 (of 6 total)

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