Adding two nested statements together

[Mr Hyde]

Got a question, it may be stupid, but regardless I don't know the answer.

 

Background: I'm trying to develop a system that searches product names for matches and ranks by relevance. I plan on calculating the relevance value by totaling the occurrences of each word in the query and giving a higher rank to matches that occur less frequently in my data set. So, if I want to calculate the relative word rank for "foo" when search "foo bar" I will find something like  (count(foo) + count(bar))/count(foo)) (obviously this is pseudo code). My problem is that I can find count(foo) and count(bar) easy enough, but I can't get MySQL to add the two columns... here is the query I ran:

 

mysql> SELECT
    ->     id,
    ->     name,
    ->     (
    ->         SELECT
    ->             count(*)
    ->         FROM `search_index`
    ->         WHERE
    ->             `name` REGEXP '[[:<:]]burton[[:>:]]' = 1 AND
    ->             `table` = 'c5'
    ->     ) as w1,
    ->     (
    ->         SELECT
    ->             count(*)
    ->         FROM `search_index`
    ->         WHERE
    ->             `name` REGEXP '[[:<:]]bindings?[[:>:]]' = 1 AND
    ->             `table` = 'c5'
    ->     ) as w2,
    ->     (w1+w2) as total
    -> FROM `search_index`
    -> WHERE
    ->     `name` REGEXP '[[:<:]]burton[[:>:]]' = 1 AND
    ->     `table` = 'c5'
    ->     OR `name` REGEXP '[[:<:]]bindings?[[:>:]]' = 1 AND
    ->     `table` = 'c5' LIMIT 10;
ERROR 1054 (42S22): Unknown column 'w1' in 'field list'

 

The error is pretty explicit as far as what is going wrong. I just want to know how to restructure my query to fix it. Pseudo code, and untested answers are perfectly acceptable, if pointed the right direction I'm more than capable of figuring things out by myself.

 

Btw, I'm not interested in fulltext searches for a multitude of reasons... I'm happy to explain why, but that would probably be a good subject for a different thread.

[AbraCadaver]

Untested thought:

 

mysql> SELECT
    ->     id,
    ->     name,
    ->     (
    ->         SELECT
    ->             count(*)
    ->         FROM `search_index`
    ->         WHERE
    ->             `name` REGEXP '[[:<:]]burton[[:>:]]' = 1 AND
    ->             `table` = 'c5'
    ->     )
    ->     +
    ->     (
    ->         SELECT
    ->             count(*)
    ->         FROM `search_index`
    ->         WHERE
    ->             `name` REGEXP '[[:<:]]bindings?[[:>:]]' = 1 AND
    ->             `table` = 'c5'
    ->     ) as total
    -> FROM `search_index`
    -> WHERE
    ->     `name` REGEXP '[[:<:]]burton[[:>:]]' = 1 AND
    ->     `table` = 'c5'
    ->     OR `name` REGEXP '[[:<:]]bindings?[[:>:]]' = 1 AND
    ->     `table` = 'c5' LIMIT 10;

[Mr Hyde]

Yeah, that will work. I found through further research that you can't reuse an alias in the select statement. My only recourse is to duplicate queries... which offends my sense of aesthetics, but it works. I could also create a view, I guess, but I would like to avoid locking the table. Thanks for the response.