?
Solved

Transact-SQL Group By Clause

Posted on 2003-03-13
4
Medium Priority
?
622 Views
Last Modified: 2011-09-20
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
Comment
Question by:scottsanpedro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 1

Expert Comment

by:Aycex
ID: 8127647
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8127981
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 8131022
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
 
LVL 1

Author Comment

by:scottsanpedro
ID: 8134902
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question