CODEWITHSUNDEEP
sqlsql-servert-sql
JM1
JM1

Reputation: 1715

TSQL- How to count number of instances

I'm using SQL Server 2014 and I need to know which set of students have an action code used only once, and which students have an action code used more than once.

enter image description here

I'm trying to see if a student has more than one of 5 action codes.
e.g. 

SELECT StudentID 
FROM #T 
WHERE ActionCode IN (51201,51206,51207,51208,51209)

How would I code this please?

Thank you for your help.

Upvotes: 1

Views: 249

Answers (3)

ErikE
ErikE

Reputation: 50251

SELECT
   StudentID,
   ActionCode,
   ActionCodeCount = Count(*)
FROM
   dbo.StudentActionCodes
WHERE
   ActionCode IN (51201, 51206, 51207, 51208, 51209)
GROUP BY
   StudentID,
   ActionCode
ORDER BY
   StudentID DESC,
   ActionCode
;

See this working in a live demo at SqlFiddle

Given this set of values (including some unwanted ActionCodes):

StudentID   ActionCode
----------- -----------
987654      51201
987654      51206
987654      51207
987654      51208
987654      51209
987654      51210
987653      51201
987653      51208
987653      51208
987653      51211
987652      51201
987652      51206
987652      51206
987652      51207
987652      51208
987652      51209
987652      51212
987652      51213
987651      51201
987651      51206
987651      51209
987651      51209
987651      51214
987651      51215
987650      51201
987650      51201
987650      51201
987650      51201
987650      51208
987650      51208
987650      51216

Here are the results:

StudentID   ActionCode  ActionCodeCount
----------- ----------- ---------------
987654      51201       1
987654      51206       1
987654      51207       1
987654      51208       1
987654      51209       1
987653      51201       1
987653      51208       2
987652      51201       1
987652      51206       2
987652      51207       1
987652      51208       1
987652      51209       1
987651      51201       1
987651      51206       1
987651      51209       2
987650      51201       4
987650      51208       2

You might also enjoy this query:

SELECT
   *
FROM
   dbo.StudentActionCodes S
   PIVOT (
      Count(S.ActionCode) FOR S.ActionCode IN ([51201], [51206], [51207], [51208], [51209])
   ) P
;

Which gives this result:

StudentID   51201       51206       51207       51208       51209
----------- ----------- ----------- ----------- ----------- -----------
987650      4           0           0           2           0
987651      1           1           0           0           2
987652      1           2           1           1           1
987653      1           0           0           2           0
987654      1           1           1           1           1

Upvotes: 3

DhruvJoshi
DhruvJoshi

Reputation: 17136

Please try the below query: SQL fiddle link: http://sqlfiddle.com/#!6/d1dc6/6

It gives sum of same type of actions per student as well as sum of all allowed actions per student

SELECT DISTINCT
    StudentID, 
    Actioncode,
    COUNT(1) OVER (PARTITION BY StudentID,Actioncode  ) as [Count_of_this_action_instance],
    COUNT(1) OVER (PARTITION BY StudentID  ) as [Count_of_all_action_instance] FROM StudentActionCodes 
WHERE ActionCode IN (51201,51206,51207,51208,51209)

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

count and group by

SELECT
   StudentID,
   Count(*) as 'count'
FROM
   dbo.StudentActionCodes
GROUP BY
   StudentID;

Upvotes: 0

Related Questions