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!

 

[Aladin Akyurek]

Aladin,

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

regards

Yep... DESC yields of course all associated dates for the ties of MAX. I was taken by that comment I quoted. Not a big deal though...

 

[Fazza]

I am still unclear, Aladin.

The "TOP 1" (that I proposed) returns at most one record: which may be not the desired outcome. It will not return multiple records when the same maximum value is available from more than one date. So I'm glad you chimed in on the thread.

Good to see you posting in the Access forum, btw. I'm sure that if you're interested in SQL you will find it rewarding & a good match for the way you think/work with datasets.

regards

 

[Aladin Akyurek]

I am still unclear, Aladin.

The "TOP 1" (that I proposed) returns at most one record: which may be not the desired outcome. It will not return multiple records when the same maximum value is available from more than one date. So I'm glad you chimed in on the thread.

Good to see you posting in the Access forum, btw. I'm sure that if you're interested in SQL you will find it rewarding & a good match for the way you think/work with datasets.

regards

Hi Fazza

I teach databases including querying (QBF/QueryGrid) and normalization as much as spreadsheets...

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Table1</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>TRIX_ID</TH><TH bgColor=#c0c0c0 borderColor=#000000>Product</TH><TH bgColor=#c0c0c0 borderColor=#000000>Date</TH><TH bgColor=#c0c0c0 borderColor=#000000>Amount</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#eeece1 align=right>111123</TD><TD borderColor=#eeece1>Apples</TD><TD borderColor=#eeece1 align=right>7/1/2010</TD><TD borderColor=#eeece1 align=right>$2.40</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>111124</TD><TD borderColor=#eeece1>Oranges</TD><TD borderColor=#eeece1 align=right>8/2/2010</TD><TD borderColor=#eeece1 align=right>$6.34</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>111125</TD><TD borderColor=#eeece1>Grapes</TD><TD borderColor=#eeece1 align=right>8/1/2010</TD><TD borderColor=#eeece1 align=right>$5.12</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>111126</TD><TD borderColor=#eeece1>Apples</TD><TD borderColor=#eeece1 align=right>7/4/2010</TD><TD borderColor=#eeece1 align=right>$3.45</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>111127</TD><TD borderColor=#eeece1>Grapes</TD><TD borderColor=#eeece1 align=right>7/7/2010</TD><TD borderColor=#eeece1 align=right>$8.26</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>111128</TD><TD borderColor=#eeece1>Granola</TD><TD borderColor=#eeece1 align=right>8/1/2010</TD><TD borderColor=#eeece1 align=right>$4.92</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>111129</TD><TD borderColor=#eeece1>Granola</TD><TD borderColor=#eeece1 align=right>8/2/2010</TD><TD borderColor=#eeece1 align=right>$0.85</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>111130</TD><TD borderColor=#eeece1>Granola</TD><TD borderColor=#eeece1 align=right>7/2/2010</TD><TD borderColor=#eeece1 align=right>$5.55</TD></TR><TR vAlign=top><TD borderColor=#eeece1 align=right>111131</TD><TD borderColor=#eeece1>KAD</TD><TD borderColor=#eeece1 align=right>9/2/2010</TD><TD borderColor=#eeece1 align=right>$10.04</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

SELECT TOP 1 Table1.Date, Sum(Table1.Amount) AS SumOfAmount
FROM Table1
GROUP BY Table1.Date
ORDER BY Sum(Table1.Amount) DESC;

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Fazza</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Date</TH><TH bgColor=#c0c0c0 borderColor=#000000>SumOfAmount</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5 align=right>9/2/2010</TD><TD borderColor=#d0d7e5 align=right>$10.04</TD></TR><TR vAlign=top><TD borderColor=#d0d7e5 align=right>8/1/2010</TD><TD borderColor=#d0d7e5 align=right>$10.04</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I named the query Fazza...

By the way, Joe's set up would do the same, explicitly.

Regards,

Aladin

 

[Fazza]

Well, I am really surprised, Aladin: I had thought 'TOP 1' would return only 1 record.

I thought that is what TOP 1 does - just return (at most) 1 record. I will have to read up on it.

I assume you like SQL...

 

[Aladin Akyurek]

Well, I am really surprised, Aladin: I had thought 'TOP 1' would return only 1 record.

I thought that is what TOP 1 does - just return (at most) 1 record. I will have to read up on it.

I assume you like SQL...

I do... Alas, I have to do it with Query Grid at my school.

 

[Fazza]

Showing my ignorance, I don't know what Query Grid is!

I've learnt what SQL I do know from solving questions I've encountered using Excel: I apply it also in MS Access. I directly enter the SQL and scratch around until it works. Even seemingly basic SQL is very powerful &, I find, enjoyable.

regards

 

[Fazza]

Google found http://stackoverflow.com/questions/887787/access-sql-using-top-5-returning-more-than-5-results

And at a casual reading, this seems to be a consistent http://www.mrexcel.com/forum/showthread.php?t=496073
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>


</TD></TR></TBODY></TABLE>

 

[Aladin Akyurek]

Showing my ignorance, I don't know what Query Grid is!

I've learnt what SQL I do know from solving questions I've encountered using Excel: I apply it also in MS Access. I directly enter the SQL and scratch around until it works. Even seemingly basic SQL is very powerful &, I find, enjoyable.

regards

Query grid is similar to Query by form (QBF) in Ingres and other kindred systems (Oracle, DB2, etc.). I tell my students that it's a visiual query language (VQL)...

 

[Aladin Akyurek]

Google found http://stackoverflow.com/questions/887787/access-sql-using-top-5-returning-more-than-5-results

And at a casual reading, this seems to be a consistent http://www.mrexcel.com/forum/showthread.php?t=496073
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=64>



</TD></TR></TBODY></TABLE>

Advanced Filter and Pivot Tables will all behave the same: That is, also show the ties of the Nth value. There is seldom any reason to restrict the results to "strictly Top N." You can find many threads in Excel Questions on Top N.