How to send multiple select sql query?

Question

Sql queries are sent to:

SELECT COUNT(*) as success_total,  SUM(AMOUNT) AS success_Amount 
FROM success_log  WHERE TRANSACTION = 'success' 
UNION 
SELECT COUNT(*) as Fail_total, SUM(AMOUNT) AS Fail_Amount 
FROM success_log  WHERE TRANSACTION = 'cancel' 
UNION 
SELECT COUNT(*) as total, SUM(ABS(AMOUNT)) AS Total_Amount 
FROM success_log

in this result:

but I want result:

How do I send a query to get the results I want?

Select all desired data into multiple variables and then select that variable at the end. — Kaushik, Mar 05 '20 at 06:05
Try pivot join, please visit- https://stackoverflow.com/questions/17073134/sql-server-join-tables-and-pivot — Akash Babu, Mar 05 '20 at 07:05
Consider handling issues of data display in application code — Strawberry, Mar 05 '20 at 07:50

score 4 · Answer 1 · answered Mar 05 '20 at 06:04

SELECT *
FROM 
( SELECT COUNT(*) as success_total,  SUM(AMOUNT) AS success_Amount 
  FROM success_log  WHERE TRANSACTION = 'success' ) t1
CROSS JOIN
( SELECT COUNT(*) as Fail_total, SUM(AMOUNT) AS Fail_Amount 
  FROM success_log  WHERE TRANSACTION = 'cancel' ) t2
CROSS JOIN 
( SELECT COUNT(*) as total, SUM(ABS(AMOUNT)) AS Total_Amount 
  FROM success_log ) t3

score 3 · Answer 2 · answered Mar 05 '20 at 06:10

Try this :

SELECT SUM(CASE WHEN TRANSACTION = 'success' THEN 1 ELSE 0 END) AS success_total,
       SUM(CASE WHEN TRANSACTION = 'success' THEN AMOUNT ELSE 0 END) AS success_Amount,
       SUM(CASE WHEN TRANSACTION = 'cancel' THEN 1 ELSE 0 END) AS Fail_total,
       SUM(CASE WHEN TRANSACTION = 'cancel' THEN AMOUNT ELSE 0 END) AS Fail_Amount,
       COUNT(*) AS total, SUM(ABS(AMOUNT)) AS Total_Amount
FROM success_log;

Fiddle : https://www.db-fiddle.com/f/c67eG8snLddicLLWiRSf9N/0

score 1 · Answer 3 · answered Mar 05 '20 at 08:54

In MySQL you can do aggregation :

SELECT SUM(transaction = 'success') AS success_total,
       SUM(CASE WHEN transaction = 'success' THEN amount ELSE 0 END) AS success_Amount,
       SUM(transaction = 'cancel') AS Fail_total,
       SUM(CASE WHEN transaction = 'cancel' THEN amount ELSE 0 END) AS Fail_Amount,
       COUNT(*) AS total, 
       SUM(ABS(amount)) AS Total_Amount
FROM success_log sl;

How to send multiple select sql query?

3 Answers3