Summarizing Two Conditions On The Same SQL Table

Given a SQL table Transactions ID INT COMPANY_ID INT STATUS INT where STATUS IN (0,1) indicates a free transaction and STATUS IN (2,3) indicates a billa

Solution 1:

Here is a start, I think this is along the right lines...The only thing left would be to add the ratio.

SELECT
    COMPANY_ID,
    NON_BILLABLE = SUM(CASE STATUS WHEN IN (0, 1) THEN 1 ELSE 0 END),
    BILLABLE = SUM(CASE STATUS WHEN IN (2, 3) THEN 1 ELSE 0 END)
FROM TRANSACTIONS
GROUP BY COMPANY_ID

EDIT: for standards compliance.

SELECT
    COMPANY_ID,
    SUM(CASE STATUS WHEN IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE,
    SUM(CASE STATUS WHEN IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE
FROM TRANSACTIONS
GROUP BY COMPANY_ID