Multiple aggregate functions in SQL Server

Question

I need a query for [Contribution]. I used this query:

with ttt as     
(    
select 
(DYG.U_StyleId)[DYG Style]    
 ,Max(O1.CardCode) [Party Group Code],
MAX(O1.CardName) [Party Group Name]    
,MAX(OR1.DocDate) [Date]    
,sum(CONVERT(NUMERIC(15,2),(RDR1.PriceBefDi*RDR1.Quantity))) [JobAmount]    
,CONVERT(NUMERIC(15,2),SUM(RDR1.Quantity)) [Mtr]    
,CONVERT(NUMERIC(15,2),SUM(RDR1.U_Pcs))[Pcs]    
,(select sum(RDR1.PriceBefDi*RDR1.Quantity) from RDR1)  tqty    

from 
ORDR OR1    
left join RDR1 on RDR1.DocEntry = OR1.DocEntry     
left join OITM on RDR1.ItemCode = oitm.ItemCode    
LEFT JOIN OCRD ON OCRD.CardCode = OR1.CardCode    
LEFT JOIN OCRG ON OCRG.GroupCode = OCRD.GroupCode    
LEFT JOIN OCRD O1 ON O1.U_BCode = OCRD.U_GrpCod
LEFT JOIN 
( SELECT U_StyleId FROM RDR1 WHERE U_StyleId in 
('BLOOM','BLOOMING','DYD','DYD-R','DYED','Ex.CLR.','RAINBOW'))
 DYG ON DYG.U_StyleId = RDR1.U_StyleId    
    group by 
       DYG.U_StyleId    
                     ) 
   select 
    Style, [Party Group Code],
    [Party Group Name], JobAmount,
    (sum(JobAmount) / tqty * 100) [Contribution],
    [Date], [Pcs] 
from
    ttt 
  group by 
        Style

I need Sum of last jobamount to divide it with above tqty. But it shows this error.

'Column 'ttt.Party Group Code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

Please help me with the query to get right [Contribution] amount.

Ruslan K. · Accepted Answer · 2017-09-25 08:24:16Z

1

Try this:

select Style,[Party Group Code],[Party Group Name],JobAmount,[Date],[Pcs],
        100.0 * (sum(JobAmount) OVER (PARTITION BY Style))/tqty [Contribution]
    from ttt;

answered Sep 25, 2017 at 8:24

Ruslan K.

2,0011 gold badge16 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple aggregate functions in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related