CODEWITHSUNDEEP
sql-servert-sql
vinnynh
vinnynh

Reputation: 29

Query from the result of another query

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

Upvotes: 0

Views: 58

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I turn to window functions for things like this. Here is one approach:

select pc.*
from (select pc.*,
             dense_rank() over (order by product_sales desc, product_id) as product_rank
      from (select sd.product_id, sd.custno, sum(sd.sales$) as total_sales,
                   row_number() over (partition by sd.product_id order by sum(sd.sales$) as cust_within_product_rank,
                   sum(sum(sd.sales$)) over (partition by sd.product_id) as product_sales
            from salesdetails sd
            group by sd.product_id, sd.custno
           ) pc
      ) pc
where product_rank <= 150 and cust_within_product_rank <= 10;

Upvotes: 0

Pugal
Pugal

Reputation: 549

You can try cross join.

select top 105 * from salesTable where <as you wish>
cross join 
select top 10 * from customerTable where <as you wish>

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

you should be able to do it using outer apply like so.

select      *
from        (
                select      top 150 
                            product_ID, 
                            SUM(Sales$) AS Total_Sales
                from        dbo.SalesDetails sd1
                group by    product_ID
                order by    Total_Sales desc
            ) p
outer apply (
                select      top 10
                            custno,
                            SUM(Sales$) AS Total_Sales
                from        dbo.SalesDetails sd2
                where       sd2.product_ID = p.product_ID
                and         sd2.invdte >= GETDATE() - 1095
                group by    custno
                order by    Total_Sales desc

            ) c

you could probably do it with a single derived table and have 2 ranks partitioned differently.

Upvotes: 1

SUMguy
SUMguy

Reputation: 1673

    select * from (
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
    ) sales1

    LEFT OUTER JOIN

    select * from (
    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
        ) sales2 
          ON sales1.product_ID = sales2.product_ID

Upvotes: 1

Related Questions