Idontknow Idontknow - 1 year ago 88
MySQL Question

mySQL convert string date to quarter

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 |

and so on. Thanks

Answer Source

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'))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download