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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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 Architect / Systems AnalystCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.