I am looking to pull a closing balance from the database for each month.

I have tried

SELECT CloseBal As 'Balance', MONTHNAME(DateTime) as 'Month', DateTime
FROM Table1
Group By Month

I am getting an error invalud use of grouping function

What would be the best way to achieve this?

  • Read about "groupwise-max via the tag I added.
    – Rick James
    Feb 3 at 18:38

2 Answers 2


There are a few ways of doing time, depending on some details that were not provided here.

One of then is to use 'ROW_NUMBER' as said in the comment above. As an example:

   FROM (
           CloseBal As 'Balance', 
           MONTHNAME(DateTime) as 'Month', 
           ROW_NUMBER() OVER (
               PARTITION BY DATE_TRUNC('month', DateTime)
               ORDER BY DateTime DESC) as priority_row
           Table1) t1
       priority_row = 1

There are other possible solutions, but more details need to be provided.

Just an observation: for ANSI SQL, the variable 'Month' doesn't exist until the query runs. Thus, you cannot call it.

  • Thank you for your response. This looks like it could do what I am looking for, although my database doesnt seem to like PARTITION BY DATE_TRUNC('month', DateTime) I am using MariaDB What other info might you need from me to fine tune the answer? Essentially, I am looking to the query to group by Month name... Find the last entry for each moth using the DateTime` field and take the closing Balance which is noted in this field. The answer above by @user580950 gives me the Month and MaxDate but the closing balance is incorrect as it takes the first entry in the month Feb 4 at 18:41
  • I tried DATEDIFF in place of DATE_TRUNC It gives me only the closing Balance for February and leaves out January Feb 4 at 18:51
  • I think I have found that the priority row is causing the issue when using DATEDIFF )I Have also tried using DATE_FORMAT which gives the same result. Priority_row returns only one answer. Ideally I would like an output for each month. Feb 4 at 19:01

The error I think it because you're using the MAX function in the WHERE clause, try the following

SELECT CloseBal As 'Balance', MONTHNAME(DateTime) as 'Month', MAX(DateTime) as 'MaxDate'
FROM Table1

You can fine-tune the statement by joining the table with a subquery that returns the MAX DateTime for each MONTHNAME(DateTime):

SELECT t1.CloseBal As 'Balance', MONTHNAME(t1.DateTime) as 'Month', MAX(t1.DateTime) as 'MaxDate'
FROM Table1 t1
SELECT MONTHNAME(DateTime) as Month, MAX(DateTime) as MaxDate
FROM Table1
) t2
ON MONTHNAME(t1.DateTime) = t2.Month AND t1.DateTime = t2.MaxDate

This should give you the closing balance that matches the MAX(DateTime) for each month.

  • Thank you for your response. This function returns the last entry date MAX(DateTime) as 'MaxDate' However, the balance it shows is the first balance in the month rather than the balance that matches the `MAX(DateTime). Is there a way to fine tune this? Feb 4 at 18:41
  • @Paulmcf1987 I have updated the answer
    – user580950
    Feb 5 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.