Home Query from the result of another query
Reply: 0

Query from the result of another query

user1191
1#
user1191 Published in May 25, 2018, 2:22 pm

I am trying to create a query to list the top 150 products based on sales$ and the top 10 customers of each product. I am able to create two queries, the first being the top 150 products based on sales $ and the other one is the total sales of each customer by products but how do I combine them together into one query so that the result will have 1500 records, showing the top 10 customers of each of the top 150 products?

First up is the Customer Sales rank by

SELECT     
    product_ID, custno, Total_Sales, 
    RANK() OVER (ORDER BY Total_Sales DESC) AS Sale_Rank_byCust
FROM  
    (SELECT  
         product_ID, custno, SUM(Sales$) AS Total_Sales
     FROM    
         dbo.SalesDetails
     WHERE   
         invdte >= GETDATE() - 1095
     GROUP BY 
         product_ID, custno) AS B

The second is the Top 150 products by sales $

SELECT TOP 150 
    product_ID, T_Extprice, 
    RANK() OVER (ORDER BY T_Extprice DESC) AS SalesRank
FROM  
    (SELECT 
         product_ID, SUM(Sales$) AS T_Extprice, product
     FROM  
         dbo.SalesDetails AS SD
     GROUP BY 
         product_ID) AS A
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.348348 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO