Query SQL databases

What you need here is just GROUP BY and COUNT for each group, but before use the group by you need to cast the datetime to:

• date with DATE function to get counts for each days.
• Year to get yearly count.
• month (or monthname) to get monthly counts.

• WEEK to get weekly counts.

  SELECT DATE(startDate) AS Days, COUNT(ID) AS NumberOfCrisis
  FROM crisis
  GROUP BY DATE(startDate);
  
  ------
  
  SELECT monthname(startDate) AS Month, COUNT(ID) AS NumberOfCrisis
  FROM crisis
  GROUP BY monthname(startDate);
  
  -------
  
  SELECT YEAR(startDate) AS Year, COUNT(ID) AS NumberOfCrisis
  FROM crisis
  GROUP BY YEAR(startDate);
  
  -------
  
  SELECT Week(startDate) AS Weeks, COUNT(ID) AS NumberOfCrisis
  FROM crisis
  GROUP BY Week(startDate);
  

For the average duration for each crisis, you need to get the duration for each crisis first using TIMEDIFF:

SELECT ID, TIMEDIFF(EndDate, StartDate) AS CrisisDuration
FROM crisis

This will give you something like this:

Then you can use AVG to get the average of the duration across all the crisis:

SELECT SEC_TO_TIME(AVG(TIMEDIFF(EndDate, StartDate))) AS AverageCrisisDuration
FROM crisis;

This will give you something like:

The use of SEC_TO_TIME after average is to display the seconds from integer to hh:mm:ss format.

And to get the average for each month, year, or week, you just need to add a group by:

SELECT 
  Monthname(startdate) AS Month, 
  SEC_TO_TIME(AVG(TIMEDIFF(EndDate, StartDate))) AS AverageCrisisDuration
FROM crisis
GROUP BY Monthname(startdate);

This will give you: