4

Following up on my question summarizing-two-conditions-on-the-same-sql-table, I added a RATIO column that is simply one SUM(...) column divided by a 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

It looks nice and clean to define the RATIO like that, but also apparently forbidden by SQL.

To get the query working, I just copied the CASE statements for NON_BILLABLE and 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, cleaner (non-redundant) way to write this query?

Community
  • 1
  • 1
Eric J.
  • 147,927
  • 63
  • 340
  • 553
  • The problem is that you can't access the aliases (`NON_BILLABLE` and `BILLABLE`) inside the select clause of your query. You can only reference them in the WHERE or HAVING clauses. You *might* be able to rig it with a left join or something, but I'm not sure. – keithjgrant Nov 24 '09 at 18:06
  • 2
    According to http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error you can't access them in WHERE clauses in standard SQL. – Eric J. Nov 24 '09 at 18:39
  • Ah, right! Only table aliases are accessible in the WHERE clause. – keithjgrant Nov 24 '09 at 18:46

1 Answers1

4

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
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • This isn't an exact answer but you should be able to adapt this solution to your problem: https://stackoverflow.com/questions/372961/mysql-use-aliased-fields-in-aggregate-functions/76495570#76495570 – user2288580 Jun 17 '23 at 09:34