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

I have the following Access statement. How do I write the statement to Group By Writer, then Order By SumofSales DESC?

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

Thank you...
Rick
ES-ComponentsAsked:
Who is Participating?
 
Bill PrewConnect With a Mentor Commented:
Try these please, depending on if you want to sort Writer first, then amount, or vice versa please:

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

Open in new window


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, Writer;

Open in new window


»bp
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Between #1/1/201/#


Just curious .... is 201 correct ?
0
 
ES-ComponentsAuthor Commented:
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;
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Bill PrewCommented:
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
0
 
ste5anSenior DeveloperCommented:
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.
1
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.