Solved

Adding multiple rows to get total SQL & ASP.NET

Posted on 2007-11-29
7
1,273 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 1

Expert Comment

by:arunyeshi2000
ID: 20375495
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
ID: 20375988
Err - surely you forgot a group by clause?

0
 
LVL 1

Accepted Solution

by:
arunyeshi2000 earned 168 total points
ID: 20376100
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Assisted Solution

by:CJ_S
CJ_S earned 166 total points
ID: 20376165
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
ID: 20376368
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
ID: 21146875
Forced accept.

Computer101
EE Admin
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Display info from DB to Label in asp.net 7 44
GUID's in SQL Server 4 31
SQL Procedure 7 49
SQL query joining 6 tables in asp.net 4 29
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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