[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1302
  • Last Modified:

Adding multiple rows to get total SQL & ASP.NET

Hey

How do i add multiple rows to get a total that i can insert into a diffrent table eg

If i have a table called stat with the structure:

Uid Votes  Views
1     4         5
1     5        5
1     5       5
2     6      6
2     5     6

how can i use SQL to add up the rows and  insert them into another table.

Ie

Ranktable

Uid  Votes  Views
1     14         15
2    11          12


Thanks

Aaron:)
0
fedders_world
Asked:
fedders_world
3 Solutions
 
arunyeshi2000Commented:
Pls try this

insert into Ranktable values (select sum(Uid) as Uid,sum(Votes) Votes, sum(Views) Views from stat )

Rgds
0
 
CJ_SCommented:
Err - surely you forgot a group by clause?

0
 
arunyeshi2000Commented:
Thanks CJ :),

Yes please add a group by clause as well

insert into Ranktable values (select sum(Uid) as Uid,sum(Votes) Votes, sum(Views) Views from stat group by Uid,Votes,Views)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CJ_SCommented:
Actually you are not grouping in that statement. The statement would be:

insert into Ranktable values (select Uid, sum(Votes) Votes, sum(Views) Views from stat group by Uid)
0
 
samtran0331Commented:
Do you really need an actual Ranktable table? I would just use CJ_S query in a view...because as a separate table, think of this scenario:
1. Record gets inserted into stat table for uid = 1
2. insert into Ranktable
repeat steps 1 and 2 a few times

Ranktable is now inaccurate for uid = 1 unless you
1. Delete from Ranktable before you do the insert every time...or
2. Have some kind of autonumber field and query Ranktable with a MAX autonumber where uid = 1 or something....why not just use a view?

CREATE VIEW dbo.Ranktable
AS
SELECT     Uid, SUM(Votes) AS TotalVotes, SUM(Views) AS TotalViews
FROM         dbo.stat
GROUP BY Uid

Then when you do:
SELECT * FROM Ranktable
it would be accurate
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now