I have several fields in a sql table, including name, type, and date. In date, it is stored as a string "2016-05-01" format. I would like to count the number of occurrences of name grouped by each quarter date.
By this, I mean that name can occur multiple times in a month. I would like to count the number of times the names occur in groups of 3 months (quarters) and print the name, count, and quarter in each row.
So if for example I had the columns name, type, and date and ran the query
it could result in
Name Quarter Count
Ron | 1 | 62 |
Ron | 2 | 32 |
Ron | 3 | 45 |
Ron | 4 | 33 |
Tim | 1 | 62 |
Tim | 3 | 62 |
Assuming that my_date_column is a date You can use quarter()
select Name, QUARTER(my_date_column), count(*) frpm my_table group by name, QUARTER(my_date_column) ;
otherwise you should convert the string in date
for convert a string you can use
str_to_date('2016-09-20', '%Y-%m-%d') select Name, QUARTER(str_to_date(my_strdate_column, '%Y-%m-%d')), count(*) frpm my_table group by name, QUARTER(str_to_date(my_strdate_column, '%Y-%m-%d')) ;