CODEWITHSUNDEEP
mysqlsqldatabase
Sisir
Sisir

Reputation: 2816

How Do I get Only Sum of Values in Same Day mySQL

I have a mySQL table like this with table name transaction:

ID  |   date    |   amount  |   type    |   balance
----------------------------------------------------
1   |   day1    |   100     |   dr      |   100
2   |   day1    |   5       |   dr      |   105
3   |   day2    |   100     |   dr      |   205
4   |   day2    |   20      |   cr      |   185
5   |   day3    |   5       |   cr      |   180

.. and so on

I can get transactions using SQL query. That is no problem for me. But what I want is SQL query for

  1. Get data by Day (last 30 days)
  2. If there is multiple entry for debit (dr) in same day. It sum up.
  3. If there is multiple entry for credit (cr) in same day. It sum up.
  4. Day end balance ( all balance are calculated everytime debit or credit is done). So very last entry of the day is the end balance of the day.

Edit: Added a no 4. Forgot to add it before. I also need a day end balance.

For example

  1. For Day1 : Dr 105, Cr 0 and balance 105
  2. For Day2 : Dr 100, Cr 20 and balance 185

FYI: Date column is timestamp.

Upvotes: 3

Views: 4355

Answers (5)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

EDIT: You added some requirements to your question to get only the last balance per day, so updated the query. Since MySQL is missing ranking functions, you'll as far as I know need to do it using a dependent subquery;

SELECT 
  DATE(date) date, 
  SUM(IF(type='dr',amount,0)) dr, 
  SUM(IF(type='cr',amount,0)) cr,
  (SELECT balance FROM transaction t2 WHERE t2.date=MAX(t.date)) balance
FROM transaction t
GROUP BY DATE(date);

...or a JOIN...

SELECT 
  DATE(t.date) date, 
  SUM(IF(t.type='dr',t.amount,0)) dr, 
  SUM(IF(t.type='cr',t.amount,0)) cr, 
  t2.balance
FROM transaction t
JOIN transaction t2
  ON DATE(t.date)=DATE(t2.date)
 AND t2.date IN (SELECT MAX(date) FROM transaction GROUP BY DATE(date))
GROUP BY DATE(t.date);

Which is more efficient you'll have to test on your data, but especially the GROUP BY DATE(date) may cause some trouble so you may want to add an actual date column to the table that you can group by.

If the id is what you want to use as your detection of the last credit/debit of the day instead of date, the queries should be fairly straight forward to update.

An SQLfiddle to test both.

Upvotes: 5

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

Use GROUP BY:

SELECT DATE(date), type, SUM(amount)
FROM transaction
GROUP BY DATE(date), type

working demo

Upvotes: 6

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT
    date,
    SUM(IF(type='dr',amount,0)) Dr,
    SUM(IF(type='cr',amount,0)) Dr,
    balance as Balance
FROM transaction
WHERE date <= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY date

Upvotes: 3

Bohemian
Bohemian

Reputation: 425023

Since this is a mysql question, you can abbreviate the query:

SELECT
    DATE(date) DATE
    SUM((type='dr') * amount) as DR,
    SUM((type='cr') * amount) as CR
FROM transaction
GROUP BY DATE(date)
ORDER BY DATE(date)

Notice that I don't use a CASE or an IF etc, i just multiply the boolean test result by the amount, because in mysql, true is 1 and false is 0!

Upvotes: 1

valex
valex

Reputation: 24144

Also if you need two columns Dr and Cr in the query then:

SELECT DATE(date), 
SUM(CASE WHEN type='dr' THEN amount ELSE 0 END ) as DR,
SUM(CASE WHEN type='cr' THEN amount ELSE 0 END ) as CR

FROM transaction
GROUP BY DATE(date)
ORDER BY DATE(date)

Upvotes: 1

Related Questions