I have the following query: 

```
select a.clei, a.partNumber, 
(
  SELECT count(*) FROM 
    ( SELECT * from search_upload_data s where a.clei is not null AND a.clei = s.clei 
      UNION
      SELECT * from search_upload_data s where a.partNumber is not null AND a.partNumber = s.partNumber 
    ) as t1
) as total
from api_analytics_data a

```

It is throwing the error: 

```
Error Code: 1054. Unknown column 'a.clei' in 'where clause'

```

I guess MySQL won't allow a join reference in the inner SQL, but I'm not sure how to get around this. 

I need the count from search\_upload\_data, See SQL Fiddle here: <http://sqlfiddle.com/#!9/172ac/2>

<=========================================================================>

**ADDITIONAL NOTE**

I need to figure out how to do this with a UNION. The OR conditions in everyone's answers work on a small scale, but bog down on the actual DB with a lot of data. 

Here is the explain (for Rajat's answer):

[![enter image description here](https://i.stack.imgur.com/hmKvP.png)](https://i.stack.imgur.com/hmKvP.png)