sum of the count(*) values in sql

Question

0.00/5 (No votes)

See more:

I have the following query now i need the sum of the count(*) values how can i do so

SQL

select a.col1 , count(*) count_1  , sum(count_1)
from table1 a join table2 on a.id = b.id

Posted 8-Aug-13 2:34am

lalitkr

Updated 8-Aug-13 2:36am

Maciej Los

v3

Add a Solution

Comments

Maciej Los 8-Aug-13 8:36am

Please, use text formatting.
Please, post example data and expected output.

2 solutions

Solution 1

hi lalitkr

try this

SQL

select  count(*) count_1 
from table1 a join table2 on a.id = b.id

Posted 8-Aug-13 2:37am

Rama Chandiran

Comments

Maciej Los 8-Aug-13 8:42am

Please, read a question carefully... and post proper solution.

lalitkr 8-Aug-13 12:29pm

Well my table is like this
id Col1 col2
1 2 6
2 3 6
3 1 6

now i want the result for the col2 as sum(col1) values and this will be displayed in the col2

so for this i used the query as

select xx.id , xx.col1 , xx.sum(col1) col2 from
( select a.id , cout(*) col1 from
table1 a join table2 b on a.id = b.id ) xx

on doing so i m getting repeating sum for the col1 now i want the sum in first row and for rest of coloumn
it resturn null value

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)

Maciej Los · Accepted Answer · 2013-08-08T02:49:00

I'm not sure what you want to achieve, but have a look at below example:

SQL

SELECT F1, COUNT(F1) AS CountOfF1, SUM(F2) SumOfF2
FROM (
	SELECT 1 AS F1, 2 AS F2
	UNION ALL SELECT 1, 5
	UNION ALL SELECT 1, 3
	UNION ALL SELECT 1, 4
	UNION ALL SELECT 2, 12
	UNION ALL SELECT 2, 5
	UNION ALL SELECT 2, 5
	UNION ALL SELECT 2, 15
) AS T
GROUP BY F1

SELECT COUNT(F1) AS DistinctF1Fields, SUM(Q.CountOfF1) AS SumOfCountOfF1
FROM ( 
	SELECT T.F1, COUNT(T.F1) AS CountOfF1, SUM(T.F2) SumOfF2
	FROM (
		SELECT 1 AS F1, 2 AS F2
		UNION ALL SELECT 1, 5
		UNION ALL SELECT 1, 3
		UNION ALL SELECT 1, 4
		UNION ALL SELECT 2, 12
		UNION ALL SELECT 2, 5
		UNION ALL SELECT 2, 5
		UNION ALL SELECT 2, 15
	) AS T
	GROUP BY T.F1
) AS Q

Result of 1. query:

1	4	14
2	4	37

Result of 2. query:

2	8