1. Senior Member
Join Date
Dec 1969
Posts
169

1. I have a SQL Sever database with fields including "SaleMonth"(numeric), "SaleYear"(numeric), "SalePrice"(money) and about 20000 records<BR>2. Now I have a form that I need to select between to dates, eg frommonth, fromyear and tomonth, toyear.<BR>3. Then I need to calculate the totals for "SalePrice" for each month and year combination.<BR><BR>The Final result of a search from 11 2002 to 1 2003 being as follows<BR>11 2002 \$11451<BR>12 2002 \$12452<BR>1 2003 \$21421<BR><BR>I hve got the following statement so far, but don&#039;t know how to do the sum based on the month and year...<BR><BR>SELECT SaleMonth, SaleYear, AvgOfUnitPrice<BR>FROM mydatabase<BR>WHERE (VarFromMonth &#060;= SaleMonth AND VarToMonth &#062;= SaleMonth AND VarFromYear &#060;= SaleYear AND VarToYear &#062;= SaleYear)<BR>

2. Senior Member
Join Date
Dec 1969
Posts
11,334

RE: SQL SUM Statement Help please

select salemonth, sum(avgofunitprice)<BR>from ...<BR>where ...<BR>group by salemonth, sum(avgofunitprice)

3. Senior Member
Join Date
Dec 1969
Posts
169

RE: SQL SUM Statement Help please

I don&#039;t think that is right...<BR><BR>The problem is that this is the Sum for a particulare month but doesn&#039;t link it to a year. Once I from all records from 10/2002 to 1/2004 I need it to calculate the Sum for each of those 15 months seperatly. If I use Where SaleYear = 2002,2003,2004, then it will find the earlier months is 2002 that I don&#039;t want.<BR><BR>Any other ideas ?

4. Senior Member
Join Date
Dec 1969
Posts
96,118

Yes, need to add Year to group by

That&#039;s all.<BR><BR>SELECT theMonth, theYear, SUM( whatever )<BR>...<BR>GROUP BY theYear, theMonth<BR>ORDER BY theYear, theMonth<BR><BR>[I forgot your field names, but you get the idea.]<BR><BR>By the way, how come you have separate year and month fields in the DB? Why not just a single "dateOfSale" field????? If you already have a dateOfSale field, then the separate year and month fields are redundant. For example you could do:<BR><BR>SELECT Year(dateOfSale) AS theYear, Month(dateOfSale) AS theMonth, SUM( whatever )<BR>FROM table<BR>WHERE Year(dateOfSale) BETWEEN 2001 AND 2004 <BR>AND Month(dateOfSale) BETWEEN 1 AND 3<BR>GROUP BY Year(dateOfSale), Month(dateOfSale)<BR>ORDER BY Year(dateOfSale), Month(dateOfSale)<BR>

5. Senior Member
Join Date
Dec 1969
Posts
169

RE: Yes, need to add Year to group by

Thank You! That&#039;s great.<BR><BR>Unfortunaly the database I have has about 20 000 of records and was supplied as an excel document with month and year seperate. I am importing it into SQL server and am stuck with the seperate fields.<BR><BR>I am really struggling with filtering by date now, any ideas how I can do this with what I have<BR><BR>A Search between 9/2002 and 3/2004<BR>Will filter results between 9 and 3 (giving and error result) and 2002 and 2004 (giving 2002, 2003, 2004).<BR>or<BR>A Search between 2/2002 and 10/2003<BR>will only give me the months between 2 and 10 for those to years and skip the months of 11,12 & 1<BR><BR>How could I write the WHERE (or HAVING) statement to correctly filter those as a date to include the correct dates. <BR>Sorry this is difficult to explain.<BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

make the WHERE more complex

WHERE ( theYear = 2002 AND theMonth &#062;= 9 )<BR> OR ( theYear = 2003 )<BR> OR ( theYear = 2004 AND theMonth &#060;= 3 )<BR><BR>But...<BR><BR>But if the table *also* has a single date field, I&#039;d just start using it and drop the usage of theMonth and theYear fields.<BR><BR>Worst come to worst, I&#039;d be tempted to *add* a field to the table. A datetime field. And then do something like<BR> UPDATE table <BR> SET monthYearDateField = <BR> CONVERT( datetime, CONVERT(varchar(2), theMonth) + &#039;/1/&#039; + CONVERT(varchar(4), theYear) )<BR><BR>See that? The added field gets<BR> &#039;9/1/2002&#039; <BR>after that is converted to a datetime. And so on. Now you can simply do<BR> WHERE monthYearDateField BETWEEN &#039;9/1/2002&#039; AND &#039;3/31/2004&#039;<BR> GROUP BY Year(monthYearDateField), Month(monthYearDateField)<BR>and it should be somewhat more efficient.<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
96,118

If you need code...

...to generate that WHERE, something like this should work:<BR><BR>&#060;%<BR>startDate = #9/1/2002#<BR>stopDate = #3/31/2004#<BR><BR>where = "WHERE ( theYear = " & Year(startDate) & " AND theMonth &#062;= " & Month(startDate) & ")" _<BR> & " OR ( theYear = " & Year(endDate) & " AND theMonth &#060;= " & Month(endDate) & ")"<BR><BR>For yr = Year(startDate) + 1 To Year(stopDate) - 1<BR> where = where & " OR ( theYear = " & yr & ")"<BR>Next<BR>%&#062;<BR><BR>In the case where the start and top are in same year, it won&#039;t produce the right results. If you have that possibility, you&#039;ll need to test for it separately.<BR>

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•