CODEWITHSUNDEEP
sqlsql-server
Jeff
Jeff

Reputation: 449

Using a case statement in the group by

I have the below sql. I am wanting my results to be grouped if the boolean is checked 0, but not grouped if the boolean is checked 1. Is the code below a good way of doing this? I feel like it should work but it is throwing the error "InvoicePeriodID is invalid in the select list because it is not contained in an aggraagte function or group by" Any advice? 

----insert invoices for rebates grouped by date, address, contract, rebate note, and order type
declare @invoices table(InvoicePeriodID int, InvoiceStartDate datetime, InvoiceEndDate datetime, JDEAddressNo float, ContractID int, RebateNoteID int, JDEOrderType char(2), RebateInvoiceID int)
insert @invoices (InvoicePeriodID , InvoiceStartDate , InvoiceEndDate , JDEAddressNo , ContractID , RebateNoteID, JDEOrderType)
select
  i.InvoicePeriodID
, i.InvoiceStartDate
, i.InvoiceEndDate
, i.JDEAddressNo
, i.ContractID
, i.RebateNoteID
, i.JDEOrderType
from 
  @inv i
group by
  case i.InvoiceSeparately
    when 1 then null
    when 0 then i.InvoicePeriodID
  end
, case i.InvoiceSeparately
    when 1 then null
    when 0 then i.InvoiceStartDate
  end
, case i.InvoiceSeparately
    when 1 then null
    when 0 then i.InvoiceEndDate
  end 
, case i.InvoiceSeparately
    when 1 then null
    when 0 then i.JDEAddressNo
  end
, case i.InvoiceSeparately
    when 1 then null
    when 0 then i.ContractID
  end 
, case i.InvoiceSeparately
    when 1 then null
    when 0 then i.RebateNoteId
  end
, case i.InvoiceSeparately
    when 1 then null
    when 0 then i.JDEOrderType
  end

Upvotes: 2

Views: 71

Answers (1)

BICube
BICube

Reputation: 4681

I think it is more readable and easier to do what you want this way

select
  i.InvoicePeriodID
, i.InvoiceStartDate
, i.InvoiceEndDate
, i.JDEAddressNo
, i.ContractID
, i.RebateNoteID
, i.JDEOrderType
from 
  @inv i
WHERE i.InvoiceSeparately = 0
group BY  i.InvoicePeriodID

 ,i.InvoiceStartDate
   ,i.InvoiceEndDate
  , i.JDEAddressNo
 , i.ContractID
  ,i.RebateNoteId
 , i.JDEOrderType
 UNION ALL 
 select
  i.InvoicePeriodID
, i.InvoiceStartDate
, i.InvoiceEndDate
, i.JDEAddressNo
, i.ContractID
, i.RebateNoteID
, i.JDEOrderType
from 
  @inv i
WHERE i.InvoiceSeparately = 1

Upvotes: 1

Related Questions