CODEWITHSUNDEEP
sqlsql-server
Nicolae Ivan
Nicolae Ivan

Reputation: 31

Group By Case From Different Tables

I am trying to group by the result of a case statement.

When I group by values from the same table as such, it works without problems:

Select 
   Case 
      When @Col = 1 Then YEAR(tbl1.myDate1)
      When @Col = 2 Then YEAR(tbl1.myDate2)
   End as [Year],
   SUM(tbl1.myValue) as [Total]
From tabel1 as tbl1
Group By Case 
      When @Col = 1 Then YEAR(tbl1.myDate1)
      When @Col = 2 Then YEAR(tbl1.myDate2)
   End

But when I insert a value from a different table in the case statement, it crashes with the error as such:

tabel1.myDate1 is not contained in an aggregate function or group by clause.

tabel1.myDate2 is not contained in an aggregate function or group by clause.

Select 
   Case 
      When @Col = 1 Then YEAR(tbl1.myDate1)
      When @Col = 2 Then YEAR(tbl1.myDate2)
      When @Col = 3 Then YEAR(tbl2.myDate)
   End as [Year],
   SUM(tbl1.myValue) as [Total]
From tabel1 as tbl1
Left Join tabel2 as tbl2
    On tbl1.tbl2ID = tbl2.ID
Group By Case 
      When @Col = 1 Then YEAR(tbl1.myDate1)
      When @Col = 2 Then YEAR(tbl1.myDate2)
      When @Col = 3 Then YEAR(tbl2.myDate)
   End

I am curious as to why I get this error, as both fields are obviously contained in the group by clause.

Also, the first code sample shows that grouping by case is okay. Why is it that only when I include different tables in the case I get this error?

Upvotes: 2

Views: 73

Answers (1)

SlimsGhost
SlimsGhost

Reputation: 2909

For complex queries such as these, a fairly inexpensive technique that also makes the query more readable, is to use inline views, like this:

select Year, Sum(myValue) as Total
from (
    select 
    Case 
        When @Col = 1 Then YEAR(tbl1.myDate1)
        When @Col = 2 Then YEAR(tbl1.myDate2)
        When @Col = 3 Then YEAR(tbl2.myDate)
    End as [Year],
    tbl1.myValue
    From tabel1 as tbl1
    Left Join tabel2 as tbl2
    On tbl1.tbl2ID = tbl2.ID
)
Group By Year

Notice how the two-table case statement is done in the inline view, and then the grouping is done as a second pass. Important to note that the second pass is NOT doing any additional I/O, which is why it is relatively inexpensive (and handy) to use this technique.

Good luck!

Upvotes: 2

Related Questions