Mysql expert - query issue??

[osgood_]

I have a database table like below: Note the different years

date.                             month.                             year

2022-01-05.                 January.                           2022   

2022-01-12.                 January.                           2022   

2023-01-08.                 January.                           2023

2022-02-18.                 February.                         2022 

2022-02-26.                 February.                         2022 

2022-02-14.                 February.                         2022 

2024-02-14.                 February.                         2024 

2022-02-19.                 April.                               2022 

2022-02-26.                 February.                         2022

2023-02-23.                 April.                               2023 

2023-02-14.                 April.                               2023

2024-02-10.                 February.                         2024 

What I need is to compile the list into the correct month/year order AND get how many occurances the month appears, example for January 2022  (2 twice). I would like the query to return the list in month/year order and get the count for each month:

January 2022 (2)

February 2022 (3)

April 2022 (1)

January 2023 (1)

April 2023 (2)

February 2024 (2)

I can get the month/year order by using the below query but can't seem to find a way of getting the 'count' per month:

SELECT DISTINCT month(date), year(date), monthname(date) FROM `dates_prices` ORDER BY year(date) , month(date) ASC

I CAN get the count but the month/year order is incorrect.

I can do without out it (it's just a bit of decoration) but if anyone has done this previously and its a simple query well it would be nice to include it.

I've seen sql query examples of categories along with count but that seems to be easier as it doesn't need to go in month/year order, which is critical to this particular need.

cheers 

Os

[osgood_ · Accepted solution]

Persistence and trial and error eventually pays off:

SELECT count(month), month(date), year(date), monthname(date) FROM dates_prices
GROUP BY year(date), month(date), monthname(date)
ORDER BY year(date), month(date) ASC

How the **** are you meant to know all this stuff - this game is totally ******* mental!!! No other profession can require this much wide spread knowledge to get anything done, it's endless!!!!