summing retrieved row values?
Here is my table:
Date Group Value1
1/1/01 AA 700
1/1/01 AA 400
1/1/01 AA 300
2/1/01 AA 200
2/1/01 AA 100
2/1/01 AA 400
1/1/01 BB 200
1/1/01 BB 300
1/1/01 BB 200
2/1/01 BB 300
2/1/01 BB 700
2/1/01 BB 400
I need to be able to add up all "Value1"s for each distinct "Group" for any "Date" I choose. So if I select "Date" 2/1/01, say from a dropdown menu, I need to display on a web page:
AA 700 (the sum of 200+100+400)
BB 1400 (the sum of 300+700+400)
But of course my table is much larger and I actually need to add up "Value1"s, "Value2"s ... up and up, and there are hundreds of distinct "Group"s.
Is there a "best" way to do something like this? Thanks a LOT for any pointers!!
(I couldn't even begin to figure out an appropriate title for this post, nor how to display the tables nicely, sorry.)
Here is the SQL statement that will pull for one group. You probably will have to do a multiple Recordset request for each group you request, unless somebody else out there knows a better way. I'd be interested in knowing.
select SUM(myvalue) as mytotalvalue FROM testTABLE
WHERE mydate = #1/1/2001#
AND mygroup = 'aa'
you must be able to add a group by to that lot. if you just want all the aa,bb groups and the sum of all the values for that group you can do something like this
select mygroup,sum(myvalue) as mytotalvalue
group by mygroup
i missed the where bit out of that but you get the idea
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)