CODEWITHSUNDEEP
sql
Fatih Demir
Fatih Demir

Reputation: 43

Merge two SQL queries on two columns

I have two queries. I want to merge and one table and two columns.

SELECT COUNT(IsApproved) as CountNotApproved
FROM Erp_OrderReceipt 
WHERE IsApproved = 0

and

SELECT COUNT(IsApproved) as CountApproved
FROM Erp_OrderReceipt 
WHERE IsApproved = 1

Upvotes: 0

Views: 48

Answers (2)

Juan
Juan

Reputation: 3705

You can use Union and group for that

 SELECT SUM(CountApproved) CountApproved, 
        SUM(CountNotApproved) CountNotApproved
 FROM
 (
    SELECT 
        0 as CountApproved,
        COUNT(IsApproved) as CountNotApproved
    FROM Erp_OrderReceipt where IsApproved=0 
    UNION
    SELECT 
        COUNT(IsApproved) as CountApproved,
        0 as CountNotApproved
    FROM Erp_OrderReceipt where IsApproved=1
 ) u

Upvotes: 0

user330315
user330315

Reputation:

You can use conditional aggregation for this:

SELECT COUNT(case when IsApproved = 0 then 0 end) as CountNotApproved, 
       COUNT(case when IsApproved = 1 then 1 end) as CountApproved
FROM Erp_OrderReceipt;

This works because aggregate functions ignore NULL values. The result of the CASE is NULL for all rows that do not match the condition.

If your DBMS supports this you can also use the ANSI SQL filter clause:

SELECT COUNT(*) FILTER (where IsApproved = 0) as CountNotApproved, 
       COUNT(*) FILTER (where IsApproved = 1) as CountApproved
FROM Erp_OrderReceipt

Upvotes: 3

Related Questions