如何在SQL中应用group in sum [英] How do I apply sum in group by in SQL

问题描述

我在表格中有3列每列1（CUT）和第2列（GROUP），第3列（EmployeeID）

有条目如



<前lang =文字>年龄15-20岁GOOD E_123
年龄15-20岁VGOOD E_128
年龄15-20岁VGOOD E_123
年龄15-20岁BAD E_128
年龄20-25岁BAD E_124
年龄20-25岁好E_126
年龄20-25岁VGOOD E_127
年龄20-25岁坏E_124
年龄20-25岁坏E_126
年龄20-25好E_127

我想出去像



<前lang =text> col1 col2 col3 col4 col5
年龄15-20好1 4
年龄15-20岁VGOOD 2 4
年龄15-20岁BAD 1 4
年龄20-25好2 6
年龄20-25 VGOOD 1 6
年龄20-25岁坏3 6

col4是groupby EMPID

col5是col2的分组总和



代码块添加 - OriginalGriff [ /编辑]



我尝试过：

  SELECT  col1，col2，COUNT（EMPID） AS  countempid 
  FROM  table1  GROUP   BY  col1，col2  order   by  AgeGroup，EngLevel 

 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 

除了解决方案1（我不是个人相关子查询的粉丝）...



您可以在JOIN中使用（非相关）子查询。

  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 

或者你可以使用公用表表达式 [ ^ ]

;  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 
 <跨越ss =code-keyword> ORDER   BY  C1.col1，C1.col2 

或者我最喜欢使用 OVER子句 [ ^ ]

  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 

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:

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

解决方案

Try this:

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

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 ..

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[^]

;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[^]

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

这篇关于如何在SQL中应用group in sum的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！