Author |
Topic |
Sae12
Starting Member
5 Posts |
Posted - 2013-04-26 : 03:48:23
|
Hi,I am working on SQL 2005 and want to do a query to get data for top 6 'suppliers' based on the sum of 'orders' and then top 10 'sites' of each of the these top 6 suppliers again based on the sum of orders. I can do it as two seperate queries but is there a clever way of doing all in one.My table is called tbl_CFTr.Many thanks in advance. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 03:58:22
|
yep do something likeSELECT *FROM (SELECT TOP 6 supplier --WITH TIES FROM tbl_CFTr GROUP BY supplier ORDER BY SUM(OrderID) DESC )tCROSS APPLY (SELECT TOP 10 sitename --WITH TIES FROM tbl_CFTr WHERE supplier = t.supplier GROUP BY sitename ORDER BY SUM(orderid) DESC )s uncomment WITH TIES if you want to return suppliers/sites with duplicate order count tooAlso I've assumed column names so make sure you use actual column names as required------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Sae12
Starting Member
5 Posts |
Posted - 2013-04-26 : 04:34:08
|
Thank you so much STAR.It's worked great. However, if I want to include the orders as well, it gives me an error.. How do I include Orders in the final result?quote: Originally posted by visakh16 yep do something likeSELECT *FROM (SELECT TOP 6 supplier --WITH TIES FROM tbl_CFTr GROUP BY supplier ORDER BY SUM(OrderID) DESC )tCROSS APPLY (SELECT TOP 10 sitename --WITH TIES FROM tbl_CFTr WHERE supplier = t.supplier GROUP BY sitename ORDER BY SUM(orderid) DESC )s uncomment WITH TIES if you want to return suppliers/sites with duplicate order count tooAlso I've assumed column names so make sure you use actual column names as required------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 04:43:56
|
Order details are in which table? and how is it related to the above table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Sae12
Starting Member
5 Posts |
Posted - 2013-04-26 : 05:10:38
|
It's the OrderId in tbl_CFTr. I would like to include that in my final result. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 05:13:53
|
but you've counting orders right. then how can you return individually order details with it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Sae12
Starting Member
5 Posts |
Posted - 2013-04-26 : 05:35:38
|
Sorry - I think I wasn't very clear. The OrderId that you have used initially is basically number of 'orders' placed by a supplier/site.If I run your query as it is, I get 2 cols (Supplier and Site) and 60 rows (top 6 supplier * top 10 supplier sites) which is what I want. All I want is a third col 'Orders' which is SUM of orders for that supplier and site.My apologies if still not clear. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 05:50:17
|
[code]SELECT *FROM (SELECT TOP 6 supplier,SUM(OrderID) AS supplierordertotalcount --WITH TIES FROM tbl_CFTr GROUP BY supplier ORDER BY SUM(OrderID) DESC )tCROSS APPLY (SELECT TOP 10 sitename,SUM(OrderID) AS siteordercount --WITH TIES FROM tbl_CFTr WHERE supplier = t.supplier GROUP BY sitename ORDER BY SUM(orderid) DESC )s[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Sae12
Starting Member
5 Posts |
Posted - 2013-04-26 : 06:02:09
|
Thank you so much again Visakh16. This is what I exactly want :) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 06:02:41
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|