CODEWITHSUNDEEP
sqlsql-server
Tai
Tai

Reputation: 167

SQL select case with SUM?

This is probably a pretty basic question but I'm trying to produce a histogram of order total values (exclusive of shipping & tax) for a given month.

Unfortunately there's no column in the table for the total, so it needs to be calculated from the subtotal minus any discounts or applied credits.

I thought something like this might work, but I don't think the SUM expression is being evaluated correctly in the case statement as it returns only the "else" condition.

select t.range as [price range], COUNT(*) as [orders]
from (
    select case
        when SUM(o.subtotal - o.discount - o.credit) between 0 and 49.99 then '0-49.99'
        when SUM(o.subtotal - o.discount - o.credit) between 50 and 99.99 then '50-99.99'
        when SUM(o.subtotal - o.discount - o.credit) between 100 and 149.99 then '100-149.99'
        when SUM(o.subtotal - o.discount - o.credit) between 150 and 199.99 then '150-199.99'
        else '200+' end as range
    from dbo.[order] o
    where o.date_placed BETWEEN '4/1/14' AND '4/30/14') t
group by t.range

What am I doing wrong? This is in MS SQL Server btw.

Upvotes: 1

Views: 727

Answers (3)

jonasnas
jonasnas

Reputation: 3570

This should work:

select t.range as [price range], COUNT(*) as [orders]
from (
    select case
        when (o.subtotal - o.discount - o.credit) between 0 and 49.99 then '0-49.99'
        when (o.subtotal - o.discount - o.credit) between 50 and 99.99 then '50-99.99'
        when (o.subtotal - o.discount - o.credit) between 100 and 149.99 then '100-149.99'
        when (o.subtotal - o.discount - o.credit) between 150 and 199.99 then '150-199.99'
        else '200+' end as range
    from dbo.[order] o
    where o.date_placed BETWEEN '4/1/14' AND '4/30/14') t
group by t.range

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

You could make this all work within a single query too. There really is no need for a subquery to do this.

select 
    case SUM(o.subtotal - o.discount - o.credit) 
        when between 0 and 49.99 then '0-49.99'
        when between 50 and 99.99 then '50-99.99'
        when between 100 and 149.99 then '100-149.99'
        when between 150 and 199.99 then '150-199.99'
        else '200+' end 
    as PriceRange
    , COUNT(*) as [orders]
from dbo.[order] o
where o.date_placed BETWEEN '4/1/14' AND '4/30/14'
group by  case SUM(o.subtotal - o.discount - o.credit) 
    when between 0 and 49.99 then '0-49.99'
    when between 50 and 99.99 then '50-99.99'
    when between 100 and 149.99 then '100-149.99'
    when between 150 and 199.99 then '150-199.99'
    else '200+' end

Upvotes: 0

Matt
Matt

Reputation: 3680

Try this format for your case statmenets

select 
    sum(case when o.subtotal - o.discount - o.credit between 0 and 49.99 then 1 else 0 end) as bucket1,
    sum(case when o.subtotal - o.discount - o.credit between 50 and 99.99 then 1 else 0 end) as bucket2,
    sum(case when o.subtotal - o.discount - o.credit between 100 and 149.99 then then 1 else 0 end) as bucket3,
    sum(case when o.subtotal - o.discount - o.credit between 150 and 199.99 then 1 else 0 end) as bucket4,
    sum(case when o.subtotal - o.discount - o.credit >= 200 then 1 else 0 end) as bucket5

Upvotes: 2

Related Questions