CODEWITHSUNDEEP
phpmysqllaravellaravel-5
Rohan
Rohan

Reputation: 13781

How to count the number of times a year has been repeated from DATE type in mysql?

I am working with a slightly complex query where I have to count the number of times an year has come in the publication_date column. I am using the Query builder from laravel and doing something like:

$publication_years = Patent::whereIn('id', $ids)->select(DB::raw('publication_date, COUNT(YEAR(publication_date)) AS count'))->groupBy('publication_date')->get();

But this is not working. This is still using the whole date to check. How can I correct this? I have tried SUBSTR too and I also tried to convert it using CONVERT and CAST but in vain? Anyone has any idea?

Upvotes: 0

Views: 53

Answers (3)

Alok Patel
Alok Patel

Reputation: 8022

You should group by with YEAR('publication_date') instead of full date. So your query might look something like this,

$publication_years = Patent::whereIn('id', $ids)->select(DB::raw('YEAR(publication_date), COUNT(YEAR(publication_date)) AS count'))->groupBy(DB::raw('YEAR(publication_date)'))->get();

Because the following 

groupBy('publication_date')

will group by the result-set by publication_date instead of year of publication_date.

Upvotes: 1

Shrikant Mavlankar
Shrikant Mavlankar

Reputation: 1153

I'm not that familiar with laravel but this query might be helpful for you

SELECT YEAR(publication_date) AS date_year, COUNT(YEAR(publication_date)) AS date_year_count FROM `your_table` 
GROUP BY date_year

Upvotes: 1

user3575353
user3575353

Reputation: 397

My suggestion is just fetch the date field from the table 'publication_date' using a select query and check the numbers of rows to get the year count.

Upvotes: 0

Related Questions