CODEWITHSUNDEEP
sqlsql-server
Adilla Rhiskani
Adilla Rhiskani

Reputation: 31

How to combine 2 SQL statements into one table

I have 2 SQL statements to look up successful transactions and failed transactions.

SELECT COUNT (code_reseller) as trx_success, kode_reseller
FROM transaksi
where status = '20' AND CAST (date_entri AS DATE) = CAST (GETDATE() AS DATE) 
group by code_reseller
ORDER BY trx_success DESC

AND

SELECT COUNT (code_reseller) as trx_fail, kode_reseller
FROM transaksi
where status > '20' AND CAST (date_entri AS DATE) = CAST (GETDATE() AS DATE) 
group by code_reseller
ORDER BY trx_fail DESC

How to combine into one table with 3 columns result with code_reseller, trx_success and trx_fail?

Upvotes: 1

Views: 78

Answers (2)

Ajay2707
Ajay2707

Reputation: 5808

As suggested by @Dale k, you can do it like this.

You cannot add order by inside, so create an alias table and give order by condition.

SELECT *
FROM
(
    SELECT COUNT (code_reseller) as trx_success, kode_reseller
    FROM transaksi
    WHERE status = '20' AND CAST (date_entri AS DATE) = CAST (GETDATE() AS DATE) 
    GROUP BY code_reseller

    UNION ALL

    SELECT COUNT (code_reseller) as trx_fail, kode_reseller
    FROM transaksi
    WHERE status > '20' AND CAST (date_entri AS DATE) = CAST (GETDATE() AS DATE) 
    GROUP BY code_reseller
 ) a
 ORDER BY a.trx_success DESC --here we get first select query table' column name and datatype and no of column will be same required in union/union all

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Use conditional aggregation and combine the queries:

SELECT
    kode_reseller,
    COUNT(CASE WHEN status = '20' THEN 1 END) AS trx_success,
    COUNT(CASE WHEN status > '20' THEN 1 END) AS trx_fail
FROM transaksi
WHERE
    CAST(date_entri AS DATE) = CAST(GETDATE() AS DATE) 
GROUP BY
    kode_reseller;

The strategy here is to move the filtering on the status column which previously appeared in the two WHERE clauses into the conditional counts in the SELECT clause. The restriction on date_entri can stay there, since both queries have it.

Upvotes: 3

Related Questions