median ordered products - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

median ordered products

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-03-12 : 10:01:05

Hi

I currently have this query that displays the top 20 lowest qty of ordered products. But I would like to display a median value of ordered products by week (grouped by user). How can I modify the query to achieve this?


SELECT     TOP (20) dbo.tbl_Login.UserRealName, SUM(dbo.tbl_Order.Quantity) AS Qty
FROM         dbo.tbl_Order INNER JOIN
                      dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId INNER JOIN
                      dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID
WHERE     (dbo.tbl_Order.DateOrdered >= @FromDate) AND (dbo.tbl_Order.DateOrdered < @ToDate)                      
GROUP BY dbo.tbl_Login.UserRealName, dbo.tbl_Products.CustID
HAVING      (dbo.tbl_Products.CustID = @CustID)
ORDER BY Qty ASC

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-12 : 10:17:05

What is you definition of "median value of ordered products"?

CODO ERGO SUM

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-03-12 : 10:27:45

If a user order 3 products week 1 and 5 products week 2, the median for this user would be 4 products a week

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-12 : 10:35:38

quote:
Originally posted by magmo

If a user order 3 products week 1 and 5 products week 2, the median for this user would be 4 products a week

You definition of "median" sounds more like the arithmetic mean, or in other words, the average.

You can just write a query that gives the total quantity ordered each week for each user, and then use the output of that query to compute the mean using the AVG function.

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 13:24:01


SELECT     TOP (20) dbo.tbl_Login.UserRealName, SUM(dbo.tbl_Order.Quantity)*1.0/COUNT(DISTINCT DATEPART(wk,dbo.tbl_Order.DateOrdered)) AS MedianQty
FROM         dbo.tbl_Order INNER JOIN
                      dbo.tbl_Products ON dbo.tbl_Order.NodeID = dbo.tbl_Products.NodeId INNER JOIN
                      dbo.tbl_Login ON dbo.tbl_Order.UserUniqueID = dbo.tbl_Login.UID
WHERE     (dbo.tbl_Order.DateOrdered >= @FromDate) AND (dbo.tbl_Order.DateOrdered < @ToDate)                      
GROUP BY dbo.tbl_Login.UserRealName, dbo.tbl_Products.CustID
HAVING      (dbo.tbl_Products.CustID = @CustID)
ORDER BY MedianQty ASC

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-03-12 : 14:01:04

Hi visakh16

This is awsome, brilliant! Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 14:03:59

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources