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

mysql query group by date transaction

There is table called accounts containing transaction data with columns account_number, date, balance …

I want to get the balance for each account_number where the balance<-1000000000 on the last day of each account transaction, and sort the output by balance ASC.

How can I do this?

Tags:

2

Answers


  1. 0

    Use this query, just need to know which format your date.
    But assume it YYYY-MM-DD

    SELECT account_number, balance 
FROM accounts 
WHERE balance < -1000000000
AND date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
ORDER BY balance ASC
    Login or Signup to reply.
  2. 0

    query joins the "accounts" table with a subquery to efficiently retrieve the balances where the balance is less than -1,000,000,000 on the last transaction date, sorted by the balance in ascending order.

    SELECT a.account_number, a.date AS last_transaction_date, a.balance
FROM accounts AS a
JOIN (
    SELECT account_number, MAX(date) AS max_date
    FROM accounts
    GROUP BY account_number
) AS max_dates
ON a.account_number = max_dates.account_number AND a.date = max_dates.max_date
WHERE a.balance < -1000000000
ORDER BY a.balance ASC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search