sql - getting the count of employee in each grade

问题:

I am new to sql ,I have a table like this

Emp_id    |  Emp_NAME | EMP_GRADE
  1           Test1      A1
  2           Test2      A2
  3           Test3      A3
  4           Test4      A4
  6           Test5      A1
  7           Test6      A2
  8           Test7      A3

I need to get the count of the employee in each grade , in which the final ouput will be "2 - 2 - 2 - 1 " in a single column where output refers (Count of Employee in each Grade ie A1(2) - A2(2)- A3(2) -A4(1)) . can anyone give sql query for this. I hope we dont need cursor for this .

回答1:

Use:

   DECLARE @Grades varchar(1000)

  SELECT @Grades=coalesce(@Grades + ' ','') +Cast(COUNT(EMP_GRADE) as Varchar(2))+' -'  From TableName 
  Group By EMP_GRADE

  Select @Grades=SUBSTRING(@Grades,0,LEN(@Grades))
  Select @Grades

Update:

SELECT @Grades=coalesce(@Grades + ' ','') +Cast(COUNT(t1.EMP_GRADE) as Varchar(2))+' -'  From @tab1 t
Left Join @tab1 t1 On t1.EMP_GRADE= t.EMP_GRADE And  t1.Emp_id= t.Emp_id 
And t1.EMP_GRADE<>'A3' -- Replace conditions here
Group By t1.EMP_GRADE,t.EMP_GRADE

回答2:

SELECT COUNT(Emp_id) FROM myTableName GROUP BY EMP_GRADE

回答3:

This should work:

SELECT EMP_GRADE, COUNT(EMP_Id) AS EMPS_COUNT
FROM TableName
GROUP BY EMP_GRADE

Hope that helps. Keep learning SQL.

回答4:

SELECT STUFF((
  SELECT ' - ' + CAST(COUNT(1) AS VARCHAR(max)) 
  FROM myTable 
  GROUP BY EMP_GRADE 
  ORDER BY EMP_GRADE 
  FOR XML PATH('')
), 1, 3, '')

SQL Fiddle example

---

If you are filtering but still want to return results for every grade, you will need a self-join to get the full list of grades. Here's one way:

;WITH g AS (SELECT DISTINCT EMP_GRADE FROM myTable)
SELECT STUFF((
  SELECT ' - ' + CAST(COUNT(t.Emp_id) AS VARCHAR(max)) 
  FROM g
  LEFT OUTER JOIN myTable t ON g.EMP_GRADE = t.EMP_GRADE
      AND t.Emp_id % 2 = 1 --put your filter conditions here as part of the join
  GROUP BY g.EMP_GRADE
  ORDER BY g.EMP_GRADE
  FOR XML PATH('')
), 1, 3, '')

SQL Fiddle example