Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TazMania
Yak Posting Veteran
63 Posts |
 Posted - 2004-12-06 : 05:46:03
|
Hi,  I'm tryin' to write a query which calculates the total sum of a column based on a certain string, tried with sum but it doesn't return the result i want.my table looks a little like this.id indentuserno varcharprice decimalid userno price1 28780116 0.312 28780116 3.673 28800808 1.21and my asp :sql = "select price, sum(price) as totalsum from usage where userno='28780116' group by price"set rs = Conn.execute(SQL)response.write rs("totalsum")rs("totalsum") should equal 3.98but somehow this doesn't happen, rs("totalsum") only contains 0.31.i've search through the forum but hasn't found any query which i could use.Any help would be very much appreciated.Thanks.Best regardsTaz. |
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-12-06 : 06:56:30
|
quote:
...group by price
Hm...--Frankhttp://www.insidesql.de |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-12-06 : 08:27:29
|
| the "GROUP BY" clause, groups 'likeminded' (identical) values...in this case you are asking for records to be grouped on the prices....but you have no duplicates(based on the price column anyway)!!!!....In effect in this case, you are asking for detail AND SUMMARY data to be displayed for each record.I suspect that you have 2 records being returned in your resultset."select sum(price) as totalsum from usage where userno='28780116'"will achieve your aim. |
|
|
|
TazMania
Yak Posting Veteran
63 Posts |
Posted - 2004-12-06 : 09:00:09
|
Thanks Andrew, but of course...How embarressing  hehe  Best regardsTaz |
|
|
|
|
|
|
Calculate total.