How to write a GROUP BY and ORDER BY statement in Microsoft Access?

Between #1/1/201/#


Just curious .... is 201 correct ?

You were close. After I saw what you did, I tried doing the below statement and IT WORKS!!!!!


SELECT Writer, SUM(Sales) as SumofSales
FROM Bookings
WHERE (Day Between #1/1/201/# AND #12/31/2018#)
GROUP BY Writer
ORDER BY SUM(Sales) DESC;

Right, as I said it really depends on the ordering you want, it wan't crystal clear when I first read the question.

I'd always suggest ordering things when it makes sense, in your approach, the order of Writer inside the same value of SUM(Sales) in undetermined.  It could show up one way when you run the query once, and another when you run it again.  I don't like things like that, and sometimes the users don't.  So I added both to the ORDER BY so it would always reproduce the same way.

---

»bp

Caveat, @ES-Components: yours is different than Bill's solution. Bill's solution has always a definied order, yours not. Consider the case when two writers have the same sum. Then Bill's solution orders them by writer. Your result set is undefined in these cases.

You can do this as well:


SELECT Writer, SUM(Sales) as SumofSales
FROM Bookings
WHERE (Day Between #1/1/201/# AND #12/31/2018#)
GROUP BY Writer
ORDER BY 2