Solved

Adding multiple rows to get total SQL & ASP.NET

Posted on 2007-11-29
7
1,261 Views
Last Modified: 2008-03-17
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
Comment
Question by:fedders_world
7 Comments
 
LVL 1

Expert Comment

by:arunyeshi2000
Comment Utility
Pls try this

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

Rgds
0
 
LVL 22

Expert Comment

by:CJ_S
Comment Utility
Err - surely you forgot a group by clause?

0
 
LVL 1

Accepted Solution

by:
arunyeshi2000 earned 168 total points
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 22

Assisted Solution

by:CJ_S
CJ_S earned 166 total points
Comment Utility
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
 
LVL 37

Assisted Solution

by:samtran0331
samtran0331 earned 166 total points
Comment Utility
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
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Forced accept.

Computer101
EE Admin
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now