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).
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