Rohit Jachak Rohit Jachak - 1 month ago 8
SQL Question

Calculate difference of dates belonging to same group in sql

I have following set of data:

ClassName | Dates | ClassID
----------------
ClassA | 21-Jun-16 | 1238
ClassA | 27-Jun-16 | 1238
ClassB | 14-Apr-11 | 1252
ClassB | 14-Apr-11 | 1252
ClassC | 26-Oct-15 | 1261
ClassC | 21-Oct-15 | 1261


For each group of ClassName or ClassID I want to take difference of dates.

The output should have the following format-

ClassName | Days | ClassID
----------------
ClassA | 5 | 1238
ClassB | 0 | 1252
ClassC | 4 | 1261


Please suggest!
Thanks!

Answer

In most databases, you can do something like this:

select ClassName, ClassId,
       max(date) - min(date) as days
from t
group by ClassName, ClassId;

However, the logic for subtracting dates might differ among databases.