Search code examples
sqlsql-servergroup-bycase

GROUP By the CASE statement SQL Server

I would like to GROUP BY the CASE statement in SQL Server. I created cases that's my revenue range. I'd like to calculate the number of sales orders within all these revenue ranges (cases).

What I tried:

Select 
    sum(OrderQuantity) as Orders,
    case when (sum(SalesAmount-TaxAmt-Freight)<100 and sum(SalesAmount-TaxAmt-Freight)>=0) then '$0-$100'
       when (sum(SalesAmount-TaxAmt-Freight)>=100 and sum(SalesAmount-TaxAmt-Freight)<500) then '$100-$500'
       when (sum(SalesAmount-TaxAmt-Freight)>=500 and sum(SalesAmount-TaxAmt-Freight)<1000) then '$500-$1000'
       when (sum(SalesAmount-TaxAmt-Freight)>=1000 and sum(SalesAmount-TaxAmt-Freight)<2500) then '$1000-$2500'
       when (sum(SalesAmount-TaxAmt-Freight)>=2500 and sum(SalesAmount-TaxAmt-Freight)<5000) then '$2500-$5000'
       when (sum(SalesAmount-TaxAmt-Freight)>=5000 and sum(SalesAmount-TaxAmt-Freight)<10000) then '$5000-$10000'
       when (sum(SalesAmount-TaxAmt-Freight)>=10000 and sum(SalesAmount-TaxAmt-Freight)<50000) then '$10000-$50000'
       when (sum(SalesAmount-TaxAmt-Freight)>=50000 and sum(SalesAmount-TaxAmt-Freight)<100000) then '$50000-$100000'
       when (sum(SalesAmount-TaxAmt-Freight)>=100000) then '>$100000'
    end as SalesAmountCategory
From  
    dbo.FactResellerSales 
group by 
    SalesAmountCategory;

What I expect:

enter image description here

What I get:

"Invalid column name 'SalesAmountCategory'"

Solution

  • It looks like you want to know number of orders in each category

    So, you need first to map each order to category and then group by it, like that:

    select SalesAmountCategory, count(*) from
(
    Select case
        when ((SalesAmount-TaxAmt-Freight)>=100000) then '>$100000'
        when ((SalesAmount-TaxAmt-Freight)>=50000) then '$50000-$100000'
        when ((SalesAmount-TaxAmt-Freight)>=10000) then '$10000-$50000'
        when ((SalesAmount-TaxAmt-Freight)>=5000) then '$5000-$10000'
        when ((SalesAmount-TaxAmt-Freight)>=2500) then '$2500-$5000'
        when ((SalesAmount-TaxAmt-Freight)>=1000) then '$1000-$2500'
        when ((SalesAmount-TaxAmt-Freight)>=500) then '$500-$1000'
        when ((SalesAmount-TaxAmt-Freight)>=100) then '$100-$500'
        when ((SalesAmount-TaxAmt-Freight)<100) then '$0-$100'
        end as SalesAmountCategory
    From  dbo.FactResellerSales 
) as t
group by SalesAmountCategory