CODEWITHSUNDEEP
mysqlsql
Akaash
Akaash

Reputation: 77

Minus credit from debit column in same query?

I have following Query:

SELECT user_id, SUM( credit ) AS cred, SUM( debit ) AS deb FROM account WHERE user_id = '35'

I want to minus debit from credit and show it in one column or as a value in a variable

Upvotes: 1

Views: 3519

Answers (2)

John Woo
John Woo

Reputation: 263723

you can calculate the difference in the same line

SELECT  user_id, 
        SUM(credit) AS cred, 
        SUM(debit) AS deb,
        SUM(credit) -  SUM(debit) total
FROM account 
WHERE user_id = 35

UPDATE 1

SELECT  user_id, 
        SUM(credit) AS cred, 
        SUM(debit) AS deb,
        SUM(credit) -  SUM(debit) total
FROM account 
WHERE user_id IN (30,31,32,33,34,35)
GROUP BY user_ID

UPDATE 2

SELECT  user_id, 
        SUM(credit) AS cred, 
        SUM(debit) AS deb,
        SUM(credit) -  SUM(debit) total
FROM account 
WHERE user_id IN (30,31,32,33,34,35)
GROUP BY user_ID
HAVING (SUM(credit) -  SUM(debit)) < 10

Upvotes: 1

Kermit
Kermit

Reputation: 34055

This should work and is probably slightly faster than doing two other SUMs 

SELECT a.user_id, 
       a.cred, 
       a.deb, 
       a.deb - a.cred AS value 
FROM   (SELECT user_id, 
               Sum(credit) AS cred, 
               Sum(debit)  AS deb 
        FROM   account 
        WHERE  user_id = '35') a

If you have multiple users, you can do something like:

SELECT a.user_id, 
       a.cred, 
       a.deb, 
       a.deb - a.cred AS value 
FROM   (SELECT user_id, 
               Sum(credit) AS cred, 
               Sum(debit)  AS deb 
        FROM   account 
        WHERE  user_id IN (35, 36, 39)
        GROUP BY user_id) a

See the demo

Upvotes: 3

Related Questions