Link to home
Start Free TrialLog in
Avatar of scottsanpedro
scottsanpedro

asked on

Transact-SQL Group By Clause

I am writing a rather complicated query and have run into problems..
I seem to have the query correct so far but i'm unsure how to resolve
the problem of what the call the group by clause.  Here's the query
I'm only concerned with the area surrounded by ***(added for viewing
purposes only)
I need to name this part in the Group By Clause.

My Question..do I need to add the whole select part into the end of the
Group BY or can I give it an alise??

CREATE Procedure ROYALGetStockByPeriod

@StartPeriod int  , @EndPeriod int , @StockCode varChar(20)

As
     SELECT SM_STOCK_CODE, SM_Location ,          
     SUM(CASE SM_Status
          WHEN 'I' THEN (SM_Quantity * -1)
          ELSE SM_Quantity
            END) As TotalQty,
     ***CASE SM_Location
          WHEN 'ORC' THEN CASE WHEN SUM(CASE SM_Status
                         WHEN 'I' THEN (SM_Quantity * -1)
                         ELSE SM_Quantity
                               END) / SUM(CASE SM_Status
                              WHEN 'I' THEN (DET_NETT * -1)
                              ELSE DET_NETT
                                     END) > ST_CalcPrice THEN  SUM(CASE SM_Status
                                                  WHEN 'I' THEN (SM_Quantity * -1)
                                                  ELSE SM_Quantity
                                                        END) / SUM(CASE SM_Status
                                                  WHEN 'I' THEN (DET_NETT * -1)
                                                  ELSE DET_NETT
                                                         END) *10 / 100
                                             ELSE
                                                    SUM(CASE SM_Status
                                                  WHEN 'I' THEN (SM_Quantity * -1)
                                                  ELSE SM_Quantity
                                                        END) / SUM(CASE SM_Status
                                                  WHEN 'I' THEN (DET_NETT * -1)
                                                  ELSE DET_NETT
                                                         END) *5 / 100
                                                  END
                                                  END,     ***
     DET_NETT As SellingPrice
     FROM GMCPUBS.dbo.STK_MOVEMENTS
     INNER JOIN GMCPUBS.dbo.SL_PL_NL_Detail
     ON GMCPUBS.dbo.STK_MOVEMENTS.SM_Order_Link = GMCPUBS.dbo.SL_PL_NL_Detail.DET_Order_Link
     INNER JOIN Royalties.dbo.tblStockMain
     ON GMCPUBS.dbo.STK_MOVEMENTS.SM_Stock_Code = Royalties.dbo.tblStockMain.ST_Code
     WHERE SM_STOCK_CODE = @StockCode
     AND SM_PERIODNUM >=
     CASE SM_Location
     WHEN 'Lew' THEN @StartPeriod
     WHEN 'ORC' THEN @StartPeriod
     WHEN 'SCH' THEN @StartPeriod
     WHEN 'STE' THEN (@StartPeriod - 3)
     WHEN 'CAP' THEN (@StartPeriod - 3)    
     END
     AND SM_PERIODNUM <=
     CASE SM_Location
     WHEN 'Lew' THEN @EndPeriod
     WHEN 'ORC' THEN @EndPeriod
     WHEN 'SCH' THEN @EndPeriod
     WHEN 'STE' THEN (@EndPeriod- 3)
     WHEN 'CAP' THEN (@EndPeriod- 3)    
     END    
     AND SM_Location IN('Lew','Orc','Ste','Cap','Sch')
     AND SM_Year = 'L' AND SM_Type = 'S'
     AND SM_Status In('I','O')
     GROUP BY SM_STOCK_CODE ,  SM_Location , DET_NETT
         
     RETURN


Thanks in advance

Scott
Avatar of Aycex
Aycex

All you really have to do is make sure that all fields within the select ( except those within aggregate funtions ) need to be in the group by statement.
Avatar of Scott Pletcher
It looks ugly, but you should be able to group by it by placing that entire phrase in the GROUP BY; for example:

GROUP BY SM_STOCK_CODE ,  SM_Location ,          
   
***CASE SM_Location
         WHEN 'ORC' THEN CASE WHEN SUM(CASE SM_Status
                        WHEN 'I' THEN (SM_Quantity * -1)
                        ELSE SM_Quantity
                              END) / SUM(CASE SM_Status
                             WHEN 'I' THEN (DET_NETT * -1)
                             ELSE DET_NETT
                                    END) > ST_CalcPrice THEN  SUM(CASE SM_Status
                                                 WHEN 'I' THEN (SM_Quantity * -1)
                                                 ELSE SM_Quantity
                                                       END) / SUM(CASE SM_Status
                                                 WHEN 'I' THEN (DET_NETT * -1)
                                                 ELSE DET_NETT
                                                        END) *10 / 100
                                            ELSE
                                                   SUM(CASE SM_Status
                                                 WHEN 'I' THEN (SM_Quantity * -1)
                                                 ELSE SM_Quantity
                                                       END) / SUM(CASE SM_Status
                                                 WHEN 'I' THEN (DET_NETT * -1)
                                                 ELSE DET_NETT
                                                        END) *5 / 100
                                                 END
                                                 END,     ***

DET_NETT  
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of scottsanpedro

ASKER

I think your correct. I spent the day working on this problem and came out with the same answer

CASE WHEN (SELECT ST_CalcPrice FROM Royalties.dbo.tblStockMain WHERE ST_Code = @StockCode)
          <
          SUM(CASE SM_Status
          WHEN 'I' THEN (DET_NETT * -1 )
          ELSE DET_NETT
            END) /
          SUM(CASE SM_Status
          WHEN 'I' THEN (SM_Quantity * -1)
          ELSE SM_Quantity
                 END)      
     THEN
     10
     ELSE
     5
     END As RoyPercent,

Thanks you all for your time on this

Scott