Dealing with overlapping conditions in CASE statement

Question

SELECT (CASE WHEN ymd BETWEEN CURRENT_DATE -4 AND CURRENT_DATE -1 THEN '3day total' 
WHEN ymd BETWEEN CURRENT_DATE -11 AND CURRENT_DATE -1 THEN '10day total' 
WHEN ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1 THEN '30day total' END) AS 'Period',
SUM(cost) cost
FROM table
WHERE ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1
GROUP BY 1

The result actually seems to give me Period buckets of days 1-3, days 4-10 and days 11-30. I believe this is because there are overlapping conditions and SQL stops processing the CASE statement as soon as the first condition is met.

What I want are the totals for each bucket (ie 3 day sum, 10 day sum and 30 day sum).

Is there a way to do this without adding additional fields?

PS - the syntax is a bit different then traditional sql because it's vsql (vertica).

`vsql` a front-end utility for Unix-based systems that provides meta-commands and various shell-like features that facilitate writing SQL — Kermit, Feb 28 '14 at 00:12
I don't know how Vertica handles date/time/timestamp types, but you generally [don't want to use BETWEEN with them](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). Consider timestamps (and time in general) as sequence of natural numbers - there's no "last" value before an exact integer (fractional seconds are conceptually infinite). — Clockwork-Muse, Feb 28 '14 at 03:45

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

Make three totals instead of one total, and use the condition to determine where to count each record instead of trying to determine a period for a single total:

select
  sum(case when ymd between CURRENT_DATE - 4 and CURRENT_DATE - 1 then cost else 0 end) as '3day total',
  sum(case when ymd BETWEEN CURRENT_DATE - 11 and CURRENT_DATE - 5 then cost else 0 end) as '10day total',
  sum(case when ymd BETWEEN CURRENT_DATE - 31 and CURRENT_DATE - 12 then cost else 0 end) as '30day total'
from
  table
and
  ymd between CURRENT_DATE -31 and CURRENT_DATE -1
group by
  1

Note: I'm not sure which date ranges you want to count where, so I made them so that they don't overlap, as that makes most sense if you have using between. If you still want them to overlap then you can just make a comparison instead of using between as any values later than CURRENT_DATE - 1 are already filtered out.

Edit:

To get the result in rows, you can make a union between selects:

select '3day total' as period, sum(cost) as cost
from table
where ymd between CURRENT_DATE - 4 and CURRENT_DATE - 1
union all
select '10day total', sum(cost)
from table
where ymd BETWEEN CURRENT_DATE - 11 and CURRENT_DATE - 5
union all
select '30day total', sum(cost)
from table
where ymd BETWEEN CURRENT_DATE - 31 and CURRENT_DATE - 12

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 27 '14 at 23:47

Guffa

687,336
108
737
1,005

I just noticed he left out a WHERE clause in his original SQL. I changed my answer, you might want to correct it in yours as well. – steinmas Feb 27 '14 at 23:50
I think `group by 1` is unnecessary – cha Feb 27 '14 at 23:50
It is, but it was in OP's original query. – steinmas Feb 27 '14 at 23:51
The original query was different. It had a `period` and `sum` columns. Grouped by period. What are you grouping by? – cha Feb 27 '14 at 23:52
@steinmas: I don't know what differs between vsql and SQL, I assumed that the missing `where` was one of those things. If it's not, then the OP should be able to use that part from the actual query. – Guffa Feb 27 '14 at 23:57
@cha: In regular SQL the `group by` is not needed, as it will make an implicit grouping when you use delegates. I assumed that vsql doesn't do that implicit grouping, that's why I kept it in the query. – Guffa Feb 27 '14 at 23:59
That's a good point. I assumed that the Group By wasn't necessary because it's not grouping on anything. It might not be necessary, but it definitely doesn't hurt to leave it in the query. – steinmas Feb 28 '14 at 00:04
Hi, I'm aware of this method but as stated in the OP, I'm trying to avoid creating extra columns since I'll be using many aggregate columns and having to do this for each one would be cumbersome and ugly. If there is no other way then I'll use this method. – ChrisArmstrong Mar 03 '14 at 16:59
@ChrisArmstrong: What do you mean? This doesn't add any extra fields. – Guffa Mar 03 '14 at 19:46
Instead of one cost column, this method produces three: '3 day cost', '10 day cost', '30 day cost'. I was after a single cost column with a period column which would designate the time period for each row. Does that make sense? – ChrisArmstrong Mar 03 '14 at 22:24
@ChrisArmstrong: Oh, you mean that you don't want more columns in the result. Then you can make a union between selects. I added code above. – Guffa Mar 03 '14 at 22:39

steinmas · Answer 2 · 2014-02-27T23:50:01.990

Try having a different field for each bucket. I'm assuming you want to SUM the cost for each bucket, you may need to change the THEN cost if you want to sum a different field.

You're also missing a WHERE clause in your query. The conditions start with an AND statement, you probably want to correct this.

SELECT SUM(CASE WHEN ymd BETWEEN CURRENT_DATE -4 AND CURRENT_DATE -1 THEN cost ELSE 0 END) as '3day total',
SUM(CASE WHEN ymd BETWEEN CURRENT_DATE -11 AND CURRENT_DATE -1 THEN cost ELSE 0 END) as '10day total', 
SUM(CASE WHEN ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1 THEN cost ELSE 0 END) as  '30day total' END) AS 'Period'
FROM table
WHERE ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1
GROUP BY 1

same applies to you: GROUP BY 1 is not required in your answer — cha, Feb 27 '14 at 23:55

cha · Answer 3 · 2014-02-28T00:21:14.967

0

Try to reverse your conditions:

SELECT (CASE WHEN ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1 THEN '30day total' 
WHEN ymd BETWEEN CURRENT_DATE -11 AND CURRENT_DATE -1 THEN '10day total' 
WHEN ymd BETWEEN CURRENT_DATE -4 AND CURRENT_DATE -1 THEN '3day total' 
END) AS 'Period',
SUM(cost) cost
FROM table
WHERE ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1
GROUP BY 1

edited Feb 28 '14 at 00:21

answered Feb 27 '14 at 23:46

cha

10,301
1
18
26

This had the expected effect of all the cost being bucketed in the 30day period. – ChrisArmstrong Mar 03 '14 at 17:01

Gordon Linoff · Accepted Answer · 2014-03-03T20:05:49.583

If you want to keep this as three rows with one column, here is a way:

SELECT which,
       sum(CASE WHEN ymd BETWEEN CURRENT_DATE - diff AND CURRENT_DATE -1 THEN cost
                else 0
           end) as cost
FROM table t cross join
     (select '3day total' as which, 4 as diff union all
      select '10day total', 11 union all
      select '30day total', 31
     ) w
WHERE ymd BETWEEN CURRENT_DATE -31 AND CURRENT_DATE -1
GROUP BY which;

EDIT:

The cross join works by creating three rows for each row in the original table. Each of these rows is considered for each group -- so overlaps are not an issue. The groups are defined by their name and the diff value.

In other words, if the data started with one row that was 7 days ago:

ymd                  cost
CURRENT_DATE - 7      $10

Then this gets multiplied into 3 by the cross join:

which         diff       ymd                 cost
3day total      4        CURRENT_DATE - 7     $10
10day total    11        CURRENT_DATE - 7     $10
30day total    31        CURRENT_DATE - 7     $10

Then, when these are aggregated by which, the correct values are calculated:

which         Total
3day total      $0
10day total    $10
30day total    $10

I'm getting a 0 result across each period for the cost column. Any ideas? — ChrisArmstrong, Mar 03 '14 at 16:57
@ChrisArmstrong . . . Duh! `CURRENT_DATE - diff` is something like `CURRENT_DATE - (-4)`, which is `CURRENT_DATE + 4`. Oops. A sign error. — Gordon Linoff, Mar 03 '14 at 19:27

Dealing with overlapping conditions in CASE statement

4 Answers4

Edit: