how do u calculate and display the total of a fiel

Thread: how do u calculate and display the total of a fiel

1. Junior Member
Join Date
Dec 1969
Posts
24

how do u calculate and display the total of a fiel

that is my sql statement<BR><BR>strSQL = "SELECT DISTINCTROW Members.Postcode, Members.Suburb AS Location, Count(*) AS [Number of subscribers]"<BR>strSQL = strSQL & " FROM Members GROUP BY Members.Postcode, Members.Suburb"<BR>strSQL = strSQL & " ORDER BY Members.Postcode;"<BR><BR>This is the result from the sql statement after calling the values in the stored record set.<BR><BR>Postcode Location Number of subscribers<BR>xxxxxxxx xxxxxxxx 90<BR>xxxxxxxx xxxxxxxx 89<BR><BR>what i need to know is how, do i sum up the number of subscribers in teh sql statement so that i can extrat teh stored value form teh record set and display it underneath it as a total so that it may look like this<BR><BR>--- = blanks, just using it to keep the structure right in the post.<BR><BR>Postcode Location Number of subscribers<BR>xxxxxxxx xxxxxxxx 90<BR>xxxxxxxx xxxxxxxx 89<BR><BR>Totals----------- 260<BR><BR>

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

Can't do it in a single SELECT

You either have to:<BR><BR>(1) Do two separate SELECT queries.<BR><BR>(2) Use a UNION to join two SELECTs (but then be careful about keeping the records in order so that the sum appears last).<BR><BR>(3) Do the summing in VBScript. If you are using ASP to display the results, anyway, this is by far the easiest and fastest way.<BR>

3. Junior Member
Join Date
Dec 1969
Posts
24

RE: Can't do it in a single SELECT

can u tell me how i would go about doing this, it took me days to figure out that simple method no thanks to the internest many sources of information.<BR><BR>i really need it actually shown how its done

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

Something like this

SELECT &#039;Detail&#039; AS rectype, Postcode, Suburb AS Location, Count(*) AS [Number of subscribers]<BR>FROM Members <BR>GROUP BY Postcode, Suburb<BR>UNION<BR>SELECT &#039;Overall&#039; AS rectype, NULL, NULL, Count(*) AS [Number of subscribers]<BR>FROM Members <BR>ORDER BY rectype, Postcode, Suburb<BR><BR>So that will give you something like:<BR><BR>&#039;Detail&#039; &#039;7E3 1A4&#039; &#039;Farnsdale&#039; 89<BR>&#039;Detail&#039; &#039;8F3 2B5&#039; &#039;Marquardt&#039; 57<BR>&#039;Overall&#039; NULL NULL 146<BR><BR>Assuming I coded it correctly.<BR><BR><BR>

5. Junior Member
Join Date
Dec 1969
Posts
24

RE: Something like this

didnt work, is there any easier method of doing it, im sure there is. how do rollups work.

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

WORKED perfectly...

...for me, with one *TINY* change.<BR><BR>Access insists that if you use an AS clause in the SELECT, then your ORDER BY must use the "aliased" name, not the original name.<BR><BR>I created a Table named MEMBERS with fields POSTCODE and SUBURB and used this query and it worked exactly as it should:<BR><BR>SELECT &#039;Detail&#039; AS rectype, Postcode, Suburb AS Location, Count(*) AS [Number of subscribers] <BR>FROM Members <BR>GROUP BY Postcode, Suburb <BR>UNION SELECT &#039;Overall&#039; AS rectype, NULL, NULL, Count(*) AS [Number of subscribers] <BR>FROM Members<BR>ORDER BY rectype, Postcode, Location;<BR><BR>100% correct results.<BR><BR>And what&#039;s so hard about that query, anyway?<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
•