로그인
 
홈라이브러리학습다운로드지원커뮤니티포럼
Resources for IT Professionals >
> 
> 
How to "join to groups created by GROUP BY"?

답변됨 How to "join to groups created by GROUP BY"?

  • 2012년 5월 1일 화요일 오후 3:28
     
     
    로그인하여 투표
    0

    Hi all,

    I have a GROUP BY summary query that adds up expenses by budget code over a specified time frame (by WHERE-ing out rows outside of that time frame). Works fine

    I want to extend this query by comparing the summary to budget code expenses last year, in the same time frame. I currently have this implemented by adding a join to a sub-select, which is the same query, just with the clock set back one year.

    This seems inefficient to me. This has the effect of tagging every outer query row with the same last-year total, which is eventually projected out in the outer SELECT.

    What I really want, conceptually, is to join the last-year query to the GROUPS constructed by the this-year outer summary query. What's the typical way to do this?

    thanks for any ideas,

    sff

     

모든 응답

  • 2012년 5월 1일 화요일 오후 3:31
    중재자
     
     답변됨
    로그인하여 투표
    0

    The common way of doing this will be

    select sum(case when DateColumn >=@StartOfThisYear and DateColumn < @StartOfNextColumn then Amount END) as ThisYearAmount,

    SUM(case when DateColumn > = @StartOfPreviousYear and DateColumn < @StartOfThisYear then Amount END) as [LastYearAmount],

    Item

    from Transactions

    WHERE DateColumn >=@StartOfPreviousYear and DateColumn < @StartOfNextYear

    GROUP BY Item

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • 답변으로 제안됨 Kemnet 2012년 5월 1일 화요일 오후 4:06
    • 답변으로 표시됨 sherifffruitfly2 2012년 5월 1일 화요일 오후 8:48
    •  
     
  • 2012년 5월 1일 화요일 오후 5:27
     
     답변됨
    로그인하여 투표
    0

    Would you think that PIVOT would be helpful for your requirement?

    Example -

    DECLARE @T TABLE
    (
      Expenses    INT,
      ExpenseDate DATE
    )

    INSERT
    INTO   @t VALUES
           (1000,'2011-04-29'),(1500,'2011-04-30'),(2000,'2011-05-01'),(2500,'2011-05-02')
           ,(1001,'2012-04-29'),(1501,'2012-04-30'),(2001,'2012-05-01'),(2501,'2012-05-02')

    -- Verify all table data
    SELECT * FROM   @t

    -- Verify summary data (This query is used in the pivots below without the ORDER BY)
    SELECT   YEAR(ExpenseDate)  Yr ,
             MONTH(ExpenseDate) Mon,
             SUM(Expenses)      Total
    FROM     @t
    GROUP BY YEAR(ExpenseDate),
             MONTH(ExpenseDate)
    ORDER BY YEAR(ExpenseDate),
             MONTH(ExpenseDate)        

    -- Alternate 1 - Pivot for Year
    SELECT *
    FROM   ( SELECT  YEAR(ExpenseDate) Yr ,
                    MONTH(ExpenseDate) Mon,
                    SUM(Expenses)      Total
           FROM     @t
           GROUP BY YEAR(ExpenseDate),
                    MONTH(ExpenseDate)
           ) AS qry 
           PIVOT ( SUM(Total) FOR Mon IN ([4], [5]) ) AS pvt

    -- Alternate 2 - Pivot for Month
    SELECT *
    FROM   ( SELECT  YEAR(ExpenseDate) Yr ,
                    MONTH(ExpenseDate) Mon,
                    SUM(Expenses)      Total
           FROM     @t
           GROUP BY YEAR(ExpenseDate),
                    MONTH(ExpenseDate)
           ) AS qry 
           PIVOT ( SUM(Total) FOR Yr IN ([2011], [2012]) ) AS pvt

    Query results

    -------------------------------------
    Please mark this as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam (http://aalamrangi.wordpress.com/)

    • 답변으로 표시됨 sherifffruitfly2 2012년 5월 1일 화요일 오후 8:48
    •  
     
  • 2012년 5월 1일 화요일 오후 8:48
     
     
    로그인하여 투표
    0

    Thanks for the tips both of you - I can easily see both being useful (and deffo an improvement over my (rowcount)^2  routine)!

    (Naomi's answer is pretty obvious - hate it when the obvious stuff goes over my head lol!)

    -sff

     
  • 2012년 5월 1일 화요일 오후 9:29
     
     
    로그인하여 투표
    1

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on a SQL forum.

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html\

    >> I have a GROUP BY summary query that adds up expenses by budget code over a specified time frame (by WHERE-ing out rows outside of that time frame). Works fine <<

    That is nice for you. Sure wish you were polite enough to post it.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL