Mysql expert - query issue??

LEGEND ,
Dec 31, 2021 Dec 31, 2021

Copy link to clipboard

Copied

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

 

 

 

 

Views

247

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Dec 31, 2021 Dec 31, 2021
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!!!!

Likes

Translate

Translate
LEGEND ,
Dec 31, 2021 Dec 31, 2021

Copy link to clipboard

Copied

LATEST

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!!!!

 

 

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines