CODEWITHSUNDEEP
sqlsql-servergroup-byssms
ali.bomaye
ali.bomaye

Reputation: 43

No function exists to perform desired grouping and aggregation

I've run into a conundrum I was hoping someone could help me out with. I have a table with the following fields:

SalesPeriod, SalesRep, Contract, MarginPerProduct, ProductSold

I have to multiply the MarginPerProduct and ProductSold to get 'TotalMargin'

Here is an example of the code I am trying to make work:

SELECT                                                                                   
      SalesPeriod
     , SalesRep
     , Contract
     , MarginPerProduct*ProductSold as 'TotalMargin'

FROM
     tblSales

GROUP BY
     SalesPeriod
     , SalesRep
     , Contract

Of course, the multiplied columns are invalid because they are not part of the grouping, plus there's no aggregate function being used.

Is there a way to accomplish this type of grouping when using a non recognized aggregation?

Thanks for your help.

Upvotes: 0

Views: 49

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

Why not just this 

SELECT                                                                                   
      SalesPeriod
     , SalesRep
     , Contract
     , TotalMargin = MarginPerProduct*ProductSold 
FROM
     tblSales

If you want the TotalMargin to be summed up for each SalesPeriod\SalesRep\Contract then 

SELECT                                                                                   
      SalesPeriod
     , SalesRep
     , Contract
     , TotalMargin = sum(MarginPerProduct*ProductSold) 
FROM
     tblSales
GROUP BY
     SalesPeriod
     , SalesRep
     , Contract

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271161

Presumably, you want sum():

SELECT SalesPeriod, SalesRep, Contract,
       SUM(MarginPerProduct * ProductSold) as TotalMargin
FROM tblSales
GROUP BYSalesPeriod, SalesRep, Contract;

Upvotes: 2

Related Questions