CODEWITHSUNDEEP
mysql
webdev
webdev

Reputation: 23

Need help on MySQL query, i need to get the starting balance and the end balance by date group by stock_id

I need to get the starting balance from the earliest date and the ending balance from month end and group by stock_id. My table:

id    stock_id   balance     transact_at
1     1          100         2018-06-15
2     1          70          2018-06-16
3     1          30          2018-06-31
4     2          50          2018-06-01
5     2          10          2018-03-31

I want output:

stock_id start_balance    ending_balance
1        100              30   
2        50               10

Upvotes: 0

Views: 97

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

One approach in MySQL would be to aggregate by stock_id once and find the opening and closing dates. Then, self-join twice to pull in the actual balances which occurred on those opening and closing dates.

SELECT
    t1.stock_id,
    t2.balance AS start_balance,
    t3.balance AS ending_balance
FROM
(
    SELECT
        stock_id,
        MIN(transact_at) AS min_transact_at,
        MAX(transact_at) AS max_transact_at
    FROM my_table
    GROUP BY stock_id
) t1
INNER JOIN my_table t2
    ON t1.stock_id = t2.stock_id AND t2.transact_at = t1.min_transact_at
INNER JOIN my_table t3
    ON t1.stock_id = t3.stock_id AND t3.transact_at = t1.max_transact_at;

enter image description here

Demo

Note: For posterity's sake, when MySQL 8+ becomes the norm, we could make use of things like ROW_NUMBER here, which might make it easier to get the result we want.

Upvotes: 1

Aman Chhabra
Aman Chhabra

Reputation: 3894

Try this one. In this one two inner queries are fetching starting balance and closing balance by getting minimum and maximum transact_at corresponding to a stock_id and then the parent query is combing the two queries to get starting and closing balance in an single row. I have also shared fiddle link below to try.

select 
  tabledata1.stock_id, 
  startBalance,
  closingBalance 
  from (
      select 
      table1.stock_id,
      balance as startBalance 
      from table1 join 
      (
          select stock_id,
          min(transact_at) as transact_at 
          from Table1 group by stock_id
      ) startTransaction 
      on Table1.stock_id = startTransaction.stock_id and 
      Table1.transact_at = startTransaction.transact_at
  ) tabledata1
  join (
    select 
    table1.stock_id,
    balance as closingBalance 
    from table1 join 
    (
      select stock_id,
      max(transact_at) as transact_at
      from Table1 group by stock_id
    ) endTransaction 
    on Table1.stock_id = endTransaction.stock_id 
    and Table1.transact_at = endTransaction.transact_at
  ) tabledata2 
  on tabledata1.stock_id = tabledata2.stock_id;

Demo

Upvotes: 1

Sachin Sanchaniya
Sachin Sanchaniya

Reputation: 1044

Try This One. 

SELECT stock_id,MAX(balance) as start_balance, MIN(balance) as ending_balance  FROM tbl_balance GROUP BY stock_id

Upvotes: 0

Related Questions