CODEWITHSUNDEEP
mysqldatabase
json
json

Reputation: 37

how to sum amount from two different tables in one query without using union mysql

SELECT SUM(amount_paid) AS total_session FROM session 
WHERE session.date = '2016-02-03' AND session.payment_status = 'PAID'
UNION 
SELECT SUM(amount_paid) AS total_subscription FROM subscription 
WHERE subscription.date_enrolled = '2016-02-03'  AND subscription.payment_status = 'PAID'

I just want to display the result in two different columns with alias total_session and total_subscription. How can I do that using union?

Upvotes: 2

Views: 52

Answers (2)

Barmar
Barmar

Reputation: 782693

Instead of a UNION, put each query as a subquery in a main SELECT

SELECT 
    (SELECT SUM(amount_paid) FROM session 
    WHERE session.date = '2016-02-03' AND session.payment_status = 'PAID') AS total_paid,
    (SELECT SUM(amount_paid) FROM subscription 
    WHERE subscription.date_enrolled = '2016-02-03'  AND subscription.payment_status = 'PAID') AS total_subscription

Upvotes: 1

Rahul
Rahul

Reputation: 77936

In that case, you can try doing a fake join like

SELECT xx.total_session, yy.total_subscription
FROM (
SELECT '1' as ID, SUM(amount_paid) AS total_session FROM session 
WHERE session.date = '2016-02-03' AND session.payment_status = 'PAID') xx
JOIN (SELECT '1' as ID, SUM(amount_paid) AS total_subscription FROM subscription 
WHERE subscription.date_enrolled = '2016-02-03'  AND subscription.payment_status = 'PAID') yy
ON xx.ID = yy.ID;

Upvotes: 0

Related Questions