Fatih Demir Fatih Demir - 1 year ago 84
SQL Question

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


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

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download