COUNT records in RANGE GROUP BY date

I have a sales table th开发者_如何学Goat shows the date and time of each sale.

For example:

saleid | saledate | saletime

1 | 20110327 | 101

2 | 20110327 | 102

3 | 20110328 | 201

(So sale 2 occurred on 20110327 at 102)

I need to construct a single SQL statement that:

Groups the sales by date (each row is a different date) and then

counts the sales for each time range. (With each time range being a separate column)

The table should look something like this:

saledate | 101-159 | 200-259 |

20110327 | 2 | 0 |

20110328 | 0 | 1 |

It needs to be a single statement and saledate and saletime need to remain in numeric format.

(I am pulling from a database table with several million rows)

I am using MS Access

Any advice is greatly appreciated.

Thank you so much!

---

SELECT saledate,
SUM(IIF(saletime >= 101 and saletime <= 159), 1, 0) as [101To159)
SUM(IIF(saletime >= 200 and saletime <= 259), 1, 0) as [200To259)
FROM myTable
GROUP BY saledate

Note: I haven't run this query. However, this is how it could be.

---

SELECT saledate,
SUM(case when saletime between 101 and 159 then 1 else 0 end ) as R101_159,
SUM(case when saletime between 200 and 259 then 1 else 0 end ) as R200_59
FROM myTable
GROUP BY saledate