Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding multiple rows to get total SQL & ASP.NET

Posted on 2007-11-29
7
Medium Priority
?
1,278 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 672 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 22

Assisted Solution

by:CJ_S
CJ_S earned 664 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 664 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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

660 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