mysql composite if column from composite values?

Question

I need to add an extra column to my query results,

The column needs to be called percent, If col1 == 0 then percent should contain NIS else percent should contain ceil(col2 / col1 * 100)

So I believe the following should work:

IF(col1 = 0, 'NIS', ceil(col2 / col1 * 100)) as percent

But I run into an issue as col1 and col2 are also composite's.

COUNT(distinct i.id) as col1
COUNT(distinct q.id) as col2

So I get hit with

Unknown column 'col1' in 'field list'

I could get around that issue with
IF(COUNT(distinct i.id) = 0, 'NIS', ceil(COUNT(distinct q.id) / COUNT(distinct i.id) * 100)) as percent

But that just seems like a bunch of extra processing to me, surely there is a better way around that?

Full Query

SELECT 
  `t`.*,
  `r`.`name` AS region_name,
  GROUP_CONCAT(DISTINCT p.ptype_id SEPARATOR "|") AS ptype_ids,
  COUNT(DISTINCT q.id) AS quoted,
  COUNT(DISTINCT i.id) AS intended,
  COUNT(DISTINCT qa.id) AS awarded,
  IF(
    intended = 0,
    `"NIS"`,
    CEIL(quoted / intended * 100)
  ) AS percent 
FROM
  (`tradesmen` t) 
  LEFT JOIN `regions` r 
    ON `r`.`id` = `t`.`region_id` 
  LEFT JOIN `quotes` q 
    ON `t`.`id` = `q`.`tradesman_id` 
  LEFT JOIN `quote_intentions` i 
    ON `t`.`id` = `i`.`tradesman_id` 
  LEFT JOIN `quotes` qa 
    ON `q`.`tradesman_id` = `qa`.`tradesman_id` 
    AND qa.accepted = 1 
  LEFT JOIN `ptypes_tradesmen` p 
    ON `p`.`tradesman_id` = `t`.`id` 
GROUP BY `t`.`id` 
LIMIT 20

You say "*extra processing*", but I believe MySQL will only calculate the `COUNT()` functions once and cache the result for use in each reference. Also, be aware that the data type of your resulting `percent` column will be a string even where it contains a numeric value... why not use `NULL` in place of `'NIS'`? — eggyal, Aug 28 '12 at 00:40
I think this has something to do with the order SQL statements are evaluated and that the alias is not available until later. I will be interested to see the real answer. — Adrian Cornish, Aug 28 '12 at 00:40
@eggyal That's what I was hoping as well, but either way it also looks messy, so hopefully there is a better way — Hailwood, Aug 28 '12 at 00:42
Please post the full query you ran that gave the "unknown column" error. — Ike Walker, Aug 28 '12 at 00:43
@Hailwood: The only other way would be to use a subquery that defines `col1` and `col2`, then reference those from the outer/parent query to calculate `percent`. — eggyal, Aug 28 '12 at 00:45
@eggyal eww sub queries ;) I think I might stick with my multiple `Count`'s if it came to that! — Hailwood, Aug 28 '12 at 00:48
@eggyal I guess I could use `NULLIF` for the `NIS`, If you order by (DESC) on a int column that also has null's are the nulls at the top or bottom of the list? — Hailwood, Aug 28 '12 at 00:51
From [Working with `NULL` Values](http://dev.mysql.com/doc/en/working-with-null.html): "*When doing an `ORDER BY`, `NULL` values are presented first if you do `ORDER BY ... ASC` and last if you do `ORDER BY ... DESC`.*" — eggyal, Aug 28 '12 at 00:57

eggyal · Accepted Answer · 2012-08-28T01:10:18.717

As mentioned in SELECT Syntax:

It is not permissible to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section C.5.5.4, “Problems with Column Aliases”.

Whilst the manual doesn't explicitly say so, the same reasoning applies to referring to a column alias within a select_expr.

You could place your query in a subquery to an outer one that calculates percent using the column aliases:

SELECT *, IF(intended, CEIL(quoted / intended * 100), NULL) AS percent FROM (
  SELECT 
    `t`.*,
    `r`.`name` AS region_name,
    GROUP_CONCAT(DISTINCT p.ptype_id SEPARATOR "|") AS ptype_ids,
    COUNT(DISTINCT q.id)  AS quoted,
    COUNT(DISTINCT i.id)  AS intended,
    COUNT(DISTINCT qa.id) AS awarded
  FROM
    (`tradesmen` t) 
    LEFT JOIN `regions` r 
      ON `r`.`id` = `t`.`region_id` 
    LEFT JOIN `quotes` q 
      ON `t`.`id` = `q`.`tradesman_id` 
    LEFT JOIN `quote_intentions` i 
      ON `t`.`id` = `i`.`tradesman_id` 
    LEFT JOIN `quotes` qa 
      ON `q`.`tradesman_id` = `qa`.`tradesman_id` 
      AND qa.accepted = 1 
    LEFT JOIN `ptypes_tradesmen` p 
      ON `p`.`tradesman_id` = `t`.`id` 
  GROUP BY `t`.`id` 
  LIMIT 20
) t

However, I don't think this is really worthwhile as I believe (am looking for a reference that I can cite, but nothing forthcoming yet) MySQL will only calculate each COUNT() once and use the cached result in each reference.

mysql composite if column from composite values?

Full Query

1 Answers1