Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

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
0
scottsanpedro
Asked:
scottsanpedro
1 Solution
 
AycexCommented:
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.
0
 
Scott PletcherSenior DBACommented:
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  
0
 
LowfatspreadCommented:
I think your mistake is that you should be

Sum (case when ..... )

and not

Case when x then Sum(...)

you dont't need to add the computation to the group
by clause....

you can't specify different types of aggregation for a
column in the select....
you'd need to specify multiple columns...

select sum(case when ...) , Count(distinct case when ...)

etc

hth


 
0
 
scottsanpedroAuthor Commented:
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
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now