Question 271972 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – how can i join tables by dates?

I try to get an amount by joining id and date, but I get an error in the group and the join clause.

The expected result is to bring the amount for the max date and lastdate from table 2 (so I would double join to table2 to get the amount for each date).

I tried first to get the amount for max date but don’t have any idea why it is kicking an error.

SELECT
    a.id,
    max(a.date) AS maxdate,
    max(a.date) - INTERVAL 1 DAY AS lastdate,
    b.amount
from 
table1 as a
left join table2 as b
on a.id = b.id and max(a.date) = b.date
group by a.id
Tags:

2

Answers


  1. 0

    In this line, you are trying to use max(a.date) as a join condition, which results in an error because aggregate functions such as max cannot be used directly in join conditions:

    ON a.id = b.id AND max(a.date) = b.date
    Login or Signup to reply.
  2. 0

    To get the amount for the maximum date you can use the following query:

    SELECT
  SUM(table2.amount) AS amount
FROM
  table2
  JOIN (SELECT MAX(table1.date) AS max_date FROM table1) AS t1 ON t1.max_date = table2.date
GROUP BY
  table2.date

    If you don’t need to get the total amount, you can use a query without GROUP BY:

    SELECT
  table2.amount
FROM
  table2
  JOIN (SELECT MAX(table1.date) AS max_date FROM #table1) AS t1 ON t1.max_date = table2.date
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search