Skip to main content
O
osgood_
Legend
December 31, 2021
Answered

Mysql expert - query issue??

  • December 31, 2021
  • 1 reply
  • 410 views

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

 

 

 

 

    This topic has been closed for replies.
    Correct answer osgood_

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

     

     

    1 reply

    O
    osgood_AuthorCorrect answer
    Legend
    December 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!!!!

     

     

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices