sum values in sql and show it in asp.net

Question

0.00/5 (No votes)

See more:

i have a query
which counts the records from a table

select COunt(*) from table_1

and another query
which counts the records from other table

select COunt(*) from table_2

how can I get total records
i mean that sum of both the queries

record of table_1 and record of table_2
added in single field

using only a sql query

Posted 16-Jul-12 20:12pm

Karwa_Vivek

Add a Solution

4 solutions

Solution 1

SQL

DECLARE @sum1 int
DECLARE @sum2 int

SET @Sum1 = SELECT COUNT(*) FROM Table1
SET @Sum2 = SELECT COUNT(*) FROM Table2

SELECT 
      @Sum1 As SumTable1, 
      @Sum2 as SumTable2, 
      @Sum1 + @Sum2 As Total

or just

SQL

SELECT (SELECT COUNT(*) FROM Table_1) As count1,
       (SELECT COUNT(*) FROM Table_2) As count2,
       (SELECT COUNT(*) FROM Table_1)+(SELECT COUNT(*) FROM Table_2) as total

only the total

SQL

SELECT (SELECT COUNT(*) FROM Table_1)+(SELECT COUNT(*) FROM Table_2) as total

Posted 16-Jul-12 20:15pm

StianSandberg

Updated 16-Jul-12 20:21pm

v3

Comments

Karwa_Vivek 17-Jul-12 2:21am

this will require to create a stored procedure
can this be done in a single query

StianSandberg 17-Jul-12 2:24am

updated my question with a "one liner" :)

Solution 3

Hello dude use this query.........

SQL

SELECT
(SELECT Count(*) FROM tab1  ) +
(SELECT Count(*) FROM tab2   )  as new_columnname

otherwise u will use this code as like ............]

SQL

SELECT SUM(Coun)as Total FROM (
SELECT COUNT(*) Coun FROM tab1    
UNION ALL
SELECT COUNT(*)  Coun  FROM  tab2   ) tbl

Posted 16-Jul-12 20:31pm

sanwar_mal_jat

Updated 16-Jul-12 20:38pm

v2

Solution 4

Try this :-

SQL

Select SUM(CNT) From (
Select COUNT(*) CNT From T1
UNION ALL
Select COUNT(*) CNT From  T2)

Yatin Chauhan

Posted 16-Jul-12 20:48pm

Yatin chauhan

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)

ssd_coolguy · Accepted Answer · 2012-07-16T20:23:00

Solution 2

Try below query:

SQL

SELECT SUM(cnt) FROM (
SELECT COUNT(*) cnt FROM table_1
UNION ALL
SELECT COUNT(*) cnt FROM  table_2) tbl

Posted 16-Jul-12 20:23pm

ssd_coolguy

Updated 16-Jul-12 20:49pm

v2

Comments

Karwa_Vivek 17-Jul-12 2:30am

thanks

ssd_coolguy 17-Jul-12 2:32am

Glad it helps...

StianSandberg 17-Jul-12 2:43am

This solution wont work. You'll have to add an alias after the query:
SELECT SUM(cnt) FROM (
SELECT COUNT(*) cnt FROM table_1
UNION ALL
SELECT COUNT(*) cnt FROM table_2) tbl <---

ssd_coolguy 17-Jul-12 2:51am

hei AlluvialDeposit..
yes you are right.. it's my mistake.. i tried it on oracle.. ;)
anyway. i modified it.
thank you very much.. :)