0

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
WHERE MAX(DateTime)
Group By Month

I am getting an error invalud use of grouping function

What would be the best way to achieve this?

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

2 Answers 2

0

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:

   SELECT
       Balance,
       Month,
       DateTime
   FROM (
       SELECT 
           CloseBal As 'Balance', 
           MONTHNAME(DateTime) as 'Month', 
           DateTime,
           ROW_NUMBER() OVER (
               PARTITION BY DATE_TRUNC('month', DateTime)
               ORDER BY DateTime DESC) as priority_row
       FROM 
           Table1) t1
   WHERE
       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.

3
  • 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 Commented Feb 4, 2023 at 18:41
  • I tried DATEDIFF in place of DATE_TRUNC It gives me only the closing Balance for February and leaves out January Commented Feb 4, 2023 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. Commented Feb 4, 2023 at 19:01
0

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
GROUP BY MONTHNAME(DateTime)

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
JOIN (
SELECT MONTHNAME(DateTime) as Month, MAX(DateTime) as MaxDate
FROM Table1
GROUP BY MONTHNAME(DateTime)
) t2
ON MONTHNAME(t1.DateTime) = t2.Month AND t1.DateTime = t2.MaxDate
GROUP BY MONTHNAME(t1.DateTime);

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

2
  • 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? Commented Feb 4, 2023 at 18:41
  • @Paulmcf1987 I have updated the answer Commented Feb 5, 2023 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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