Solved

Adding multiple rows to get total SQL & ASP.NET

Posted on 2007-11-29
7
1,275 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

632 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