hi how to create stored procedure to get five tables one columns total sum?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

hi am having 5 tables in my project in every table contains profit varchar(50) column so i want to sum and display in text box..can any one suggest me?

thanks

Posted 16-May-12 1:08am

ythisbug

Add a Solution

Comments

Maciej Los 16-May-12 9:51am

Do you want to sum(Field1 varchar(50))? I don't understand...
Maybe you want to group some values by Field1 and then sum(Field2 int)?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2012-05-16T01:16:00

- First of all, why are you using varchar for that? Use some numeric field.
- As a table contains several records, I suppose you dont want the sum of that column for all records in all five tables.
- You can do it in a stored procedure, a single SQL query statement, or with five standalone statements
- if you have the result you can put it in the textbox.

What is your problem exactly?

Example:

SQL

select sum (amount) from
(
select amount from t1
union
select amount from t2
union
select amount from t3
union
select amount from t4
union
select amount from t5
) as sq

Wendelius · Answer 2 · 2012-05-16T10:44:00

Even though it's not a new idea I really advice you to move away from varchar data types. If you store numbers in the database, use number data types. The same applies to all data types.

When using improper data types you just:
- create potential problems like conversion mismatches, CPU usage etc.
- enforce complex SQL
- create possible cultural problems
- create extra workload on the server and so on

As Zoltán Zörgő stated, you can use an inline view to get all the data and the sum it as you like. But I would also consider, do you really need 5 different tables?

Very often this kind of question arises because of incomplete normalization. Could you for example have an additional column in your table to define the "role" of the row and use a single table instead.

MAKReddy · Answer 3 · 2012-05-16T01:28:00

SQL

Declare @test table (COL varchar (20),   COL1 int,   COL2 int,  COL3  int,  COL4 int   )
insert into @test
Select 'AA', 150, 100 , 50  , 4 union all
Select 'HHH', 161 , 125  , 36  , 4 union all
Select 'PPPP',160 , 85 ,75  ,   4 union all
Select 'PPPP',160 , 85 ,75  ,   4 union all
Select 'JJJJJ', 120, 56 , 64 ,  2 union all
Select 'GGGG', 40, 31  ,  9 , 2

Select COL,COL1,COL2,COL3,COL4 from @test
union all
Select 'Total',sum(col1),sum(col2),sum(col3),sum(col4) from @test

hi how to create stored procedure to get five tables one columns total sum?

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0