How to use strftime Function In SQL
Example 1: GROUP BY Month
In this example, we are going to perform GROUP BY operation with month. We are going to count number of fields from the table grouping them with month.
In simple words, we are going to calculate number of rows with respect to the month they belong.
Query:
SELECT STRFTIME('%m', submission_day) AS sumission_month, COUNT(*) as month_count
FROM w3wiki
GROUP BY STRFTIME('%m', submission_day);
Output:
Explanation: We can see that all the months have their respective row count which represents the records which contains that month.
Example 2: GROUP BY Year
In this example, we are going to perform the same operation but this time we will group them with year. We are going to count all the records with respect to there years.
Query:
SELECT STRFTIME('%Y', submission_day) AS sumission_year, COUNT(*) as year_count
FROM w3wiki
GROUP BY STRFTIME('%Y', submission_day);
Output:
Explanation: As we have seen in the previous example, all the years have their respective row count value.
Example 3: GROUP BY month along with year
In this example, we are going to group all the records with respect to their month but this time we will focus on their respective year too.
Query:
SELECT STRFTIME('%m-%Y', submission_day) AS sumission_month_year, COUNT(*) as month_year_count
FROM w3wiki
GROUP BY STRFTIME('%m-%Y', submission_day);
Output:
Explanation: In the above image, we can clearly see that month and year has been displayed along with their respective row count value.
This represents the records which are present in that particular month and year. This time we have kept focus on both i.e. month and year.
How to GROUP BY Month and Year in SQLite?
SQLite is a lightweight and server–less relational database management system. It requires very minimal configurations therefore it is easy to integrate into applications. It is also considered as an ideal choice for small mobile and desktop applications. In SQLite, GROUP BY month and year is considered useful when we are dealing with some time–based analysis.
It is also useful when dealing with other tasks like data visualization, forecasting, planning, etc. In this article, We will learn about How to GROUP BY Month and Year in SQLite by understanding the various methods along with the various examples and so on.