January 22, 2009 at 2:43 am
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
January 22, 2009 at 6:45 am
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
January 22, 2009 at 7:23 am
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]
January 22, 2009 at 8:13 am
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
=====================================
dealidcustomer product category tradeMonthYear
10126my co ltd myproductleathergoodsOct-08
96127my co ltd myproductleathergoodsOct-08
62088my co ltd myproductleathergoodsNov-08
34814my co ltd myproductleathergoodsNov-08
51796my co ltd myproductleathergoodsFeb-08
10517my co ltd myproductleathergoodsJan-08
25950my co ltd myproductleathergoodsJan-08
21692my co ltd myproductleathergoodsJan-08
55644my co ltd myproductleathergoodsJul-08
55640my co ltd myproductleathergoodsJul-08
00708my co ltd myproductleathergoodsSep-08
54361my co ltd myproductleathergoodsSep-08
60690my co ltd myproductleathergoodsSep-08
86866my co ltd myproductleathergoodsMar-08
39081my co ltd myproductleathergoodsApr-08
68492my co ltd myproductleathergoodsFeb-08
96775my co ltd myproductleathergoodsMar-08
76525my co ltd myproductleathergoodsMar-08
99765my co ltd myproductleathergoodsMar-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.
January 23, 2009 at 7:06 pm
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]
February 3, 2009 at 4:57 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply