CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Eray Balkanli
Eray Balkanli

Reputation: 7990

Join tables and groupby results based on user defined parameters in sql server 2008

I want to join two tables and group by the result based on a user defined parameter in Sql Server 2008. I tried the query below but it is not working. I appreciate if someone helps me to see my mistake and correct it for me. Regards.

 Select TotalVolume= SUM(volume),
   PrimGroup = r.PrimaryGroup, SnGroup = r.SecondaryGroup
 from   Requests r 
 inner join #Calculations c on
        case @PrimaryId is not null then c.PrimaryGroup = r.PrimaryGroup end
    and case @SecondaryId is not null then c.SecondaryGroup = r.SecondaryGroup end 
    and c.SrgId = r.SrgId
 group by 
   case @PrimaryId is not null then r.PrimaryGroup end, 
   case @SecondaryId is not null then r.SecondaryGroup end

Upvotes: 0

Views: 37

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

Try this:

 Select TotalVolume= SUM(volume),
   PrimGroup = r.PrimaryGroup, SnGroup = r.SecondaryGroup
 from   Requests r 
 inner join #Calculations c on
        ((@PrimaryId is null) or 
         (@PrimaryId is not null) and c.PrimaryGroup = r.PrimaryGroup) 
    and ((@SecondaryId is null) or 
         (@SecondaryId is not null) and c.SecondaryGroup = r.SecondaryGroup) 
    and c.SrgId = r.SrgId
 group by 
   case when @PrimaryId is not null then r.PrimaryGroup end, 
   case when @SecondaryId is not null then r.SecondaryGroup end

Upvotes: 1

Related Questions