CODEWITHSUNDEEP
sqlsql-server
Emily
Emily

Reputation: 115

How do I count how many times a value has changed pertaining to a unique ID?

I am trying to figure out how many times a student has changed majors over the course of their college career. 

   Create Table Taco
  (  s_id  varchar(9),
     s_year  int,
     s_major varchar(50))

 Insert INTO Taco 
 Values (123456789, 2018, 'PSYCHOLOGY'),
        (123456789, 2016, 'GENERAL EDUCATION'),
        (123456789, 2017, 'PSYCHOLOGY'),
        (987654321, 2018, 'FILM'),
        (987654321, 2017, 'ANTHROPOLOGY'),
        (654789123, 2018, 'BIOLOGY'),
        (654789123, 2017, 'BIOLOGY'),
        (987654321, 2015, 'COMPUTER SCIENCE')

I am expecting the result:

 s_id     changes
 123456789     2      
 987654321     3
 654789123     1

How would I write this?

Upvotes: 3

Views: 2321

Answers (3)

Daniel Bürckner
Daniel Bürckner

Reputation: 363

You can just count the entries in your table and substract one to determine the amount of changes for each student.

SELECT
  s_id,
  COUNT(s_id) - 1 AS [Amount of changes]
FROM
  Taco 
GROUP BY
  s_id

I would also recommend changing the type of the last column to varchar(50) or something bigger.

See SQL fiddle: http://sqlfiddle.com/#!18/59ae2/1

Upvotes: 0

milkman
milkman

Reputation: 46

To get the number of real changes you can use LAG function to receive the last value within a group (s_id) and sum up all differences between the previous value and the current value:

SELECT t.s_id
     , SUM(CASE WHEN t.prev_val <> t.s_major OR t.prev_val IS NULL THEN 1 END) as chg_cnt
FROM
   (
       SELECT s_id
             ,s_year
            , s_major
            , LAG(s_major) OVER(PARTITION BY s_id ORDER BY s_year) prev_val
       FROM Taco
    ) as t
GROUP BY s_id

extended sqlfiddle code: http://sqlfiddle.com/#!18/61048/11

Upvotes: 3

D-Shih
D-Shih

Reputation: 46249

Use Count and GROUP BY

SELECT s_id,count(s_id) 'changes'
FROM Taco 
group by s_id

sqlfiddle:http://sqlfiddle.com/#!18/2c1d7/2

Upvotes: 3

Related Questions