Kavitha M

Kavitha M

  • NA
  • 21
  • 6.1k

Get data - Group by from inner query

May 1 2015 2:22 AM
Hi all,

I have the following query to select top 10 customers 

SELECT top 10  A.Div as 'Div'  
, A.CID as 'Customer_ID'
, A.CNAME as 'Customer_Name'
, B.REP as 'Primary_Rep'
,count(distinct B.SONO) as 'TY_Orders_Count'  
      ,  ISNULL(SUM(CASE A.SIVTYP_0
WHEN 'INV' THEN (B.QTY_0)
WHEN 'SCM' THEN (-B.QTY_0)
ELSE 0 END),0)  as 'TY_Qty_UnitCount'      
      , ISNULL(SUM( D.PRICE  *(CASE A.SIVTYP_0
WHEN 'INV' THEN (B.QTY_0)
WHEN 'SCM' THEN (-B.QTY_0)
ELSE 0 END)),0) as 'TY_COST'
from  INVOICE_TABLE   A
INNER JOIN INVDETAILS_TABLE  B on A.IID       = B.IID
    INNER JOIN PRODUCTS_TABLE    D on Year(B.INVDAT)  = D.YEA_0 and   D.PROD_ID    = B.PROD_ID  
where (B.INVDAT  between '04/10/2015' AND '04/17/2015') 

AND A.Div in ('AS','DF','GH','JH','KL')

group by
A.Div 
,A.CID
,A.CNAME
,B.REP

This query returns top 10 customers in whole data. NOw I want to get top 10 customers for each division seperately
i.e top 10 customers in 'AS',top 10 customers in 'DF',top 10 customers in 'GH',top 10 customers in 'JH',top 10 customers in 'KL'
I tried with this query, but unable to get data for eac division 

can anyone help me to do this 

Thanks in advance

Answers (1)