CODEWITHSUNDEEP
sqlms-access
grasshopper
grasshopper

Reputation: 958

Group By with Case statement?

I need find the number Sum of orders over a 3 day range. so imagine a table like this

Order      Date
300     1/5/2015
200     1/6/2015
150     1/7/2015
250     1/5/2015
400     1/4/2015
350     1/3/2015
50       1/2/2015
100     1/8/2015

So I want to create a Group by Clause that Groups anything with a date that has the same Month, Year and a Day from 1-3 or 4-6, 7-9 and so on until I reach 30 days.

It seems like what I would want to do is create a case for the grouping that includes a loop of some type but I am not sure if this is the best way or if it is even possible to combine them.

An alternative might be create a case statement that creates a new column that assigns group number and then grouping by that number, month, and Year.

Unfortunately I've never used a case statement so I am not sure which method is best or how to execute them especially with a loop.

EDIT: I am using Access so it looks like I will be using IIF instead of Case

Upvotes: 1

Views: 153

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

How about the following:

COUNT OF ORDERS

select      year([Date]) as yr,
            month([Date]) as monthofyr,
            sum(iif((day([Date])>=1) and (day([Date])<=3),1,0)) as days1to3,
            sum(iif((day([Date])>=4) and (day([Date])<=6),1,0)) as days4to6,
            sum(iif((day([Date])>=7) and (day([Date])<=9),1,0)) as days7to9,
            sum(iif((day([Date])>=10) and (day([Date])<=12),1,0)) as days10to12,
            sum(iif((day([Date])>=13) and (day([Date])<=15),1,0)) as days13to15,
            sum(iif((day([Date])>=16) and (day([Date])<=18),1,0)) as days16to18,
            sum(iif((day([Date])>=19) and (day([Date])<=21),1,0)) as days19to21,
            sum(iif((day([Date])>=22) and (day([Date])<=24),1,0)) as days22to24,
            sum(iif((day([Date])>=25) and (day([Date])<=27),1,0)) as days25to27,
            sum(iif((day([Date])>=28) and (day([Date])<=31),1,0)) as days28to31
from        tbl
where       [Date] between x and y
group by    year([Date]),
            month([Date])

Replace x and y with your date range.

The last group is days 28 to 31 of the month, so it may contain 4 days' worth of orders, for months that have 31 days.

THE ABOVE IS A COUNT OF ORDERS.

If you want the SUM of the order amounts:

SUM OF ORDER AMOUNTS

select      year([Date]) as yr,
            month([Date]) as monthofyr,
            sum(iif((day([Date])>=1) and (day([Date])<=3),order,0)) as days1to3,
            sum(iif((day([Date])>=4) and (day([Date])<=6),order,0)) as days4to6,
            sum(iif((day([Date])>=7) and (day([Date])<=9),order,0)) as days7to9,
            sum(iif((day([Date])>=10) and (day([Date])<=12),order,0)) as days10to12,
            sum(iif((day([Date])>=13) and (day([Date])<=15),order,0)) as days13to15,
            sum(iif((day([Date])>=16) and (day([Date])<=18),order,0)) as days16to18,
            sum(iif((day([Date])>=19) and (day([Date])<=21),order,0)) as days19to21,
            sum(iif((day([Date])>=22) and (day([Date])<=24),order,0)) as days22to24,
            sum(iif((day([Date])>=25) and (day([Date])<=27),order,0)) as days25to27,
            sum(iif((day([Date])>=28) and (day([Date])<=31),order,0)) as days28to31
from        tbl
where       [Date] between x and y
group by    year([Date]),
            month([Date])

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91366

Consider the Partition Function and a crosstab, so, for example:

TRANSFORM Sum(Calendar.Order) AS SumOfOrder
SELECT Month([CalDate]) AS TheMonth, Partition(Day([Caldate]),1,31,3) AS DayGroup
FROM Calendar
GROUP BY Month([CalDate]), Partition(Day([Caldate]),1,31,3)
PIVOT Year([CalDate]);

As an aside, I hope you have not named a field / column as Date.

Upvotes: 2

Related Questions