Continue my question summarizing-two-conditions-on-the-same-sql-table, I added a RATIO column, it is just a SUM(…) column divided by the second SUM(…) columns:
SELECT
COMPANY_ID,
SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE,
SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE
SUM(NON_BILLABLE)/SUM(BILLABLE) AS RATIO
FROM TRANSACTIONS< br />GROUP BY COMPANY_ID
Defining RATIO like this looks beautiful and clean, but it is obviously forbidden by SQL.
In order to make the query work, I only copied NON_BILLABLE and CASE statement for BILLABLE.
SELECT
COMPANY_ID,
SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE,
SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE
SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END)/SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS RATIO
FROM TRANSACTIONS
GROUP BY COMPANY_ID
Is there a better, clearer (non-redundant) way to write This query?
Use:
SELECT x.company_id,
x.non_billable,
x.billable,
x.non_billable/x.billable AS RATIO
FROM (SELECT t.company_id
SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE,
SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE
FROM TRANSACTIONS
GROUP BY t.company_id) x
Continue with my question summarizing-two-conditions-on-the-same-sql-table, I added a RATIO column, it Just one SUM(…) column divided by the second SUM(…) column:
SELECT
COMPANY_ID,
SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE,
SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE
SUM(NON_BILLABLE)/SUM( BILLABLE) AS RATIO
FROM TRANSACTIONS
GROUP BY COMPANY_ID
Defining RATIO like this looks beautiful and clean, but it is obviously also forbidden by SQL.
To make the query work, I only copied the NON_BILLABLE and BILLABLE CASE statements.
< p>
SELECT
COMPANY_ID,
SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE,
SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE
SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END)/SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS RATIO
FROM TRANSACTIONS
GROUP BY COMPANY_ID
Is there a better, clearer (non-redundant) way to write this query?
Use:
SELECT x.company_id,
x.non_billable,
x.billable,
x.non_billable/x.billable AS RATIO
FROM (SELECT t.company_id
SUM(CASE WHEN STATUS IN (0, 1) THEN 1 ELSE 0 END) AS NON_BILLABLE,
SUM(CASE WHEN STATUS IN (2, 3) THEN 1 ELSE 0 END) AS BILLABLE
FROM TRANSACTIONS
GROUP BY t.company_id) x
< /p>