How do I apply sum in group by in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have 3 column in a table each column 1 (CUT) and Column 2 (GROUP), column 3 (EmployeeID)
having entries like

age 15-20 GOOD  E_123
age 15-20 VGOOD E_128
age 15-20 VGOOD E_123
age 15-20 BAD   E_128
age 20-25 BAD   E_124
age 20-25 GOOD  E_126
age 20-25 VGOOD E_127
age 20-25 BAD   E_124
age 20-25 BAD   E_126
age 20-25 GOOD  E_127

I want out put like

col1  col2  col3  col4      col5
 age 15-20 GOOD    1         4
 age 15-20 VGOOD   2         4
 age 15-20 BAD     1         4
 age 20-25 GOOD    2         6
 age 20-25 VGOOD   1         6
 age 20-25 BAD     3         6

col4 is groupby EMPID
col5 is sum of group by for col2

[edit]Code block added - OriginalGriff[/edit]

What I have tried:

SQL

SELECT col1, col2, COUNT(EMPID) AS countempid
FROM table1 GROUP BY col1, col2 order by  AgeGroup,EngLevel

Posted 30-May-16 1:56am

Rahul Yadav India

Updated 30-May-16 4:53am

OriginalGriff

v2

Add a Solution

2 solutions

Solution 2

In addition to Solution 1 (I'm not personally a fan of correlated sub-queries)...

You can use (non-correlated) sub-queries in a JOIN ..

SQL

SELECT A.col1, A.col2, A.countempid, B.col5
FROM 
(	
	SELECT col1, col2, COUNT(EMPID) AS countempid
	FROM table1 GROUP BY col1, col2 
) A
INNER JOIN 
(	
	SELECT col1, COUNT(*) as col5 FROM table1 GROUP BY col1 
) B ON A.col1=B.col1
ORDER BY A.col1, A.col2

Or you could use Common Table Expressions[^]

SQL

;WITH CTE1 AS 
(
	SELECT col1, col2, COUNT(EMPID) AS countempid
	FROM table1 GROUP BY col1, col2 
), CTE2 AS
(
	SELECT col1, COUNT(*) as col5 FROM table1 GROUP BY col1 
)
SELECT C1.col1, C1.col2, C1.countempid, C2.col5
FROM CTE1 C1 
INNER JOIN CTE2 C2 ON C1.col1=C2.col1
ORDER BY C1.col1, C1.col2

Or my particular favourite using OVER clause[^]

SQL

SELECT DISTINCT col1, col2, COUNT(EMPID) OVER (PARTITION BY col1, col2) AS countempid,
COUNT(EMPID) OVER (PARTITION BY col1)
FROM table1 
ORDER BY col1, col2

Posted 30-May-16 4:53am

CHill60

Comments

Maciej Los 30-May-16 14:19pm

+5!

CHill60 30-May-16 14:51pm

Thank you!

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)

Peter Leow · Accepted Answer · 2016-05-30T04:36:00

Solution 1

Try this:

C#

SELECT col1, col2, col3, count(col3) AS col4, 
(
  SELECT count(col2) FROM tablename t2 WHERE t2.col2=t1.col2
)
AS col5 FROM tablename t1
GROUP BY col1, col2, col3

Posted 30-May-16 4:36am

Peter Leow

Updated 30-May-16 4:37am

v2

Comments

Maciej Los 30-May-16 14:19pm

5ed!

Peter Leow 30-May-16 22:19pm

Thank you, Maciej.

Rahul Yadav India 31-May-16 1:10am

Thanks dear for the solution it worked..!