Max of Sum Data

[andream02]

Hello. I'm new to SQL and am trying to write the code to show me the max sales of the summed data for a specific day based on dates in my list (the dates range for the months of October and November)

Here is what I have so far.

SELECT Date, Sum(Amount) AS SumOfAmount
FROM Transactions
GROUP BY Date

Just not sure how to get the Max in there or how to ensure I'm showing the day for a given month.

Please advise.

Thanks!

 

[Joe4]

So, do you want the maximum Sum for each month?

 

[andream02]

Below is my data set

TRX_ID Product Date Amount
111123 Apples 7/1/10 $2.40
111124 Oranges 8/2/10 $6.34
111125 Grapes 8/1/10 $5.12
111126 Apples 7/4/10 $3.45
111127 Grapes 7/7/10 $8.26
111128 Granola 8/1/10 $4.92
111129 Granola 8/2/10 $0.85
111130 Granola 7/2/10 $5.55

I would like to see the day with the highest sales.

I hope this helps!

Thanks!

 

[Joe4]

Per Month?
Per Product?

Based on your sample data that you posted, what are your expected results?

 

[andream02]

When I sum up the table I get the following:

Date SumOfAmount
7/1/2010 $2.40
7/2/2010 $5.55
7/4/2010 $3.45
7/7/2010 $8.26
8/1/2010 $10.04
8/2/2010 $7.19

I would expect to see a final result of

8/1/2010 $10.14

 

[Joe4]

OK, this is going to take a few steps.

First, I made the following assumptions (modify as needed).
I called the original table TableName, and I used all the field names you used in your example, except my date field is named DDate (you should never used reserved words like "Date" as field or variable names; as matter as fact, Access 2007 and higher will not even let you).

So, I first created a Query called Query_1 that sums each day, like this:

SELECT TableName.DDate, Sum(TableName.Amount) AS SumOfAmount
FROM TableName
GROUP BY TableName.DDate;

Then, I created another Query which uses this query to return the record with the maximum amount value:

SELECT Query_1.DDate, Query_1.SumOfAmount
FROM Query_1
WHERE Query_1.[SumofAmount]=
(SELECT Max(Query_1.SumOfAmount) AS MaxOfSumOfAmount
FROM Query_1);

This should return what you are looking for.

 

[Fazza]

Or, using as the starting point Joe's query: sort the results in descending order & take the first one. Untested

SELECT TOP 1 TableName.DDate, Sum(TableName.Amount) AS SumOfAmount
FROM TableName
GROUP BY TableName.DDate
ORDER BY Sum(TableName.Amount) DESC

 

[Aladin Akyurek]

Or, using as the starting point Joe's query: sort the results in descending order & take the first one. Untested

SELECT TOP 1 TableName.DDate, Sum(TableName.Amount) AS SumOfAmount
FROM TableName
GROUP BY TableName.DDate
ORDER BY Sum(TableName.Amount) DESC

Not just "take the first one" but all for the result list as a whole is a legitimate outcome.

 

[Fazza]

I don't understand your comment, Aladin. Can you explain it?

In case it wasn't clear, integral to taking the first one is having the data sorted in descending order. So the first one is the maximum - the solution should match that given/required (post #5).

regards

 

[Fazza]

Aladin,

Are you referring to if there are multiple days with the same (maximum) total?

regards