CODEWITHSUNDEEP
mysql
LeviZoesch
LeviZoesch

Reputation: 1621

MySQL Query Group By Date

I have tried various recommendations based off of other posts with no avail.

I have a database scheme of records with a Created_Date Key, and Value would be 01/01/2017

I am trying to query the database records to give a returned count of How many records per month and which month those fall in line with.

With the following

SELECT SQL_NO_CACHE MONTH(`Created_Date`), COUNT(*)
FROM `CRM_Leads`
GROUP BY MONTH(`Created_Date`)

I return

MONTH(`Created_Date`)   COUNT(*)    
          NULL            872

I have also tried almost all the variations on the following post

Count records for every month in a year

Any help would be appreciated.

Upvotes: 1

Views: 369

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35613

For as long as you store date/time information as strings, you will have great difficulty using any date/time specific functions and features. If you are getting NULL from MONTH(str_to_date(Created_Date, '%d/%m/%Y')) then the str_to_date isn't converting the strings to dates and the most likely reason for this is the d m y "pattern" is not corrrect.

All you have old us about your "strings that might be dates" is that one of them looks like this: 01/01/2017. Now that could be DD/MM/YYYY or MM/DD/YYYY and we simply cannot tell which one is correct from the single value you have chosen to share with us. Look for any day value greater then 12 in your data e.g. 17/01/2017 ==> DD/MM/YYYY or 01/17/2017 ==> MM/DD/YYYY

Once you have made the choice of which pattern your "strings that might be dates" follow; apply that pattern in the str_to_date() function. You migh want to try a few different patterns to get the best one (and these are just 3 of many you could try):

# which pattern is best for you?
SELECT Created_Date
     , str_to_date(`Created_Date`, '%d/%m/%Y') "d/m/y"
     , str_to_date(`Created_Date`, '%m/%d/%Y') "m/d/y"
     , str_to_date(`Created_Date`, '%Y-%m-%d') "y-m-d"
FROM `CRM_Leads`

You will not have success with your group by query until you choose the most appropriate d m y pattern to apply in teh str_to_date function. Note here that you might also have a variety of patterns in your data, in which case you have an even bigger problem to solve.

Once you have made the choice of which pattern your "strings that might be dates" follow; apply that pattern in the str_to_date() function and ONLY THEN your group by query will work.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

assuming your created_date is a string of format ('dd-mm-yyyy') the you should convert as date with str_to_date

SELECT SQL_NO_CACHE MONTH(str_to_date(`Created_Date`, '%d/%m/%Y')), COUNT(*)
FROM `CRM_Leads`
GROUP BY MONTH(str_to_date(`Created_Date`, '%d/%m/%Y'))

Upvotes: 3

Related Questions