Calculate Employee Experience from Multiple Jobs-CodePudding

I have a table named employee experience with id, userId, startDate, endDate columns.

I want to calculate employee experience. Can someone please help with mysql query or JPA specification code?

For example in case of following data:

id	userID	startDate	endDate
1	1	2021-01-01	2022-01-01
2	2	2019-01-01	2020-01-01
3	2	2020-01-02	2021-01-01
4	3	2021-01-01	2022-01-01

the output should be:

userID	experience
1	1
2	2
3	1

CodePudding user response：

If each userID represent an experience try :

select userID,count(userID) as experience
from employee_experience
group by userID;

Result:

UserID  experience
   1      1
   2      2
   3      1

Demo

CodePudding user response：

Successfully did this with the following: SELECT SUM(TIMESTAMPDIFF(YEAR, START_DATE, END_DATE)) AS experience, SOCIAL_PROFILE_ID FROM tableName GROUP BY SOCIAL_PROFILE_ID