Summing and selecting together

# Thread: Summing and selecting together

1. Junior Member
Join Date
Dec 1969
Posts
29

## Summing and selecting together

Are records selection criteria and sum statements like oil and water, or am I missing something here:<BR><BR>sql2 = "SELECT Sum(Diff1) AS Total1 FROM ServiceStats Where Month = " & "&#039;" & Request.Form("selected") & "&#039; and Year = " & "&#039;" & Request.Form("selected3") & "&#039; ORDER by ClientName "<BR><BR>Thanks<BR><BR>

2. Senior Member
Join Date
Dec 1969
Posts
2,809

## RE: Summing and selecting together

Don&#039;t you have to select the item that you order by?

3. Junior Member
Join Date
Dec 1969
Posts
29

## RE: Summing and selecting together

Oh, you&#039;re right. When all I want is to define a total, I don&#039;t care about a sort order

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

## Nope, can't do it...

Definitely oil and water.<BR><BR>Consider this set of date:<BR><BR>UserName -- rating<BR>Bill -- 9<BR>John -- 7<BR>Harry -- 10<BR>Bob -- 3<BR>Mary -- 8<BR>Jill -- 7<BR><BR>Now consider what results you could *EXPECT* if you did a query such as<BR><BR>SELECT UserName, Sum(Rating) FROM table<BR><BR>What would you *expect* to see???<BR><BR>Bill -- 44<BR>John -- 44<BR>Harry -- 44<BR>Bob -- 44<BR>Mary -- 44<BR>Jill -- 44<BR><BR>??? Does that make the vaguest sort of sense? What does it mean for Mary to be associated with 44, which is the same number John is associated with???<BR><BR>Besides, in order to *get* that Sum number, the query processor has to loop through all the records. In order to output data of that form, it would have to loop through *twice*. Once to get the sum and then again to output that weird data format.<BR><BR>

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

## Sorry! Ignore other post!

I misread what you wrote. Doh.<BR><BR>But I still see what looks to me like some bugs in that SELECT:<BR><BR>Why do you put month and year in &#039;...&#039; marks????<BR><BR>*Surely* those are numeric field? If so, you don&#039;t want the &#039;...&#039;!<BR><BR>And MONTH and YEAR are both reserved words in Access and SQL Server. If you use reserved words as field names, you usually need to wrap them in [...].<BR><BR>So...<BR><BR>SQL = "SELECT Sum(Diff1) AS Total1 FROM ServiceStats Where [Month] = & Request.Form("selected") & " and [Year] = " & Request.Form("selected3") <BR><BR><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
•