October 22, 2009 at 11:08 am
i am trying to calculate median.
I am able to calculate avg, min ,max etc. How can i calculate median in this query.
Here is my query
Select Week, avg(total) as average, min(total) as minimum, max(total) as maximum, SUM(Total) as AttemptedTotalRecords, sum(lead) as TotalRecords
FROM
(
select week=DATEADD(wk, DATEDIFF(wk, 7, calldate), 7),
count(I3_RowID) as total, 1 As Lead,
From
test
where
(CallDate between '10/1/09' and '10/22/09')
and (reason<>'deleted' or reason<>'wrongparty' or reason<>'Success' or reason<>'Failure')
Group by DATEADD(wk, DATEDIFF(wk, 7, calldate), 7), i3_rowid
) a
Group by Week order by week
The data looks like this when i run the query. i am missing the median
week avg min max attempted rec total rec median
2009-09-28 1 2 5 500 400 ?
2009-10-05 5 4 8 400 525
October 22, 2009 at 11:18 am
Hi, you may find the following useful: http://www.simple-talk.com/sql/t-sql-programming/median-workbench/
October 22, 2009 at 11:43 am
how do i add the median query to it
October 22, 2009 at 3:07 pm
If you can post some sample data from your sub query:
select week=DATEADD(wk, DATEDIFF(wk, 7, calldate), 7),
count(I3_RowID) as total, 1 As Lead,
From
test
where
(CallDate between '10/1/09' and '10/22/09')
and (reason<>'deleted' or reason<>'wrongparty' or reason<>'Success' or reason<>'Failure')
Group by DATEADD(wk, DATEDIFF(wk, 7, calldate), 7), i3_rowid
I can take a look at how.
Allister
October 22, 2009 at 3:32 pm
This is how the sample data looks
week Total lead
2009-10-05 2 1
2009-10-19 1 1
2009-10-05 1 1
2009-10-12 3 1
2009-10-12 2 1
October 22, 2009 at 7:27 pm
rs, have a look at the first link in my signature. It will help you with formatting your data so that it is readily consumable by the folks here. Make sure you include your expected output so that we can test any solutions we might suggest.
Unfortunately SQL Server doesn't come with a median function, at least without analysis services anyhow, so we're forced to write our own. There are a number of ways to do this, most of which are covered in the article that Allister gave you the link for. IF you provide us some better sample data we should be able to help you make use of one of those methods...
-Luke.
October 23, 2009 at 11:31 am
One hint would be to take the last CTE sample provided on the link.
Combine that with your subquery. My preference would be to take the subquery out and combine it into the CTE Code (from the examples at the linke provided by Allister) and put it into a temp table and then select from the temp table - if you are using SQL 2k - else if you are using 2k5 - leave it in a CTE.
WITH SortedData (x, hi, lo)
AS
(SELECT x,
ROW_NUMBER() OVER(ORDER BY x ASC),
ROW_NUMBER() OVER(ORDER BY x DESC)
FROM RawData)
SELECT AVG(x * 1.0) AS median
FROM SortedData
WHERE hi IN (lo, lo+1, lo-1);
This should get you along enough to be able to see how they could be combined.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 23, 2009 at 4:46 pm
thanks for ur help folks
October 23, 2009 at 8:04 pm
How many distinct items do you need to calculate a median for? In other words, do you have to calculate a median for every "grouped" item in a table? You know, something like "what is the median daily sales amount for each sales person?"
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply