MySQL access subquery column name in main query where clause

Question

trying to put a condition in the main where clause that checks a value from a subquery.

SELECT total, (select my_count from x where .. etc) as XXX

FROM sales

WHERE XXX > 0;

I get an unknown column error. Any ideas why?

score 2 · Answer 1 · answered Apr 02 '12 at 00:07

2

What about

select total, xxx
from (
    SELECT total, (select my_count from x where .. etc) as XXX
    FROM sales
     )
WHERE XXX > 0;

answered Apr 02 '12 at 00:07

Amir Pashazadeh

If I try this I get kind of error and can't fix http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias – Brettski Apr 02 '12 at 00:40
@Brettski: Just assign an alias to the FROM subselect, like in: `… FROM ( … ) some_alias WHERE …` – Andriy M Apr 02 '12 at 06:10
Wow this is a great answer... thanks for posting it. Very helpful. – shasi kanth Apr 16 '19 at 10:40

score 0 · Answer 2 · answered Apr 02 '12 at 00:10

0

This is a really strange query.
Why don't you put the WHERE condition inside the subquery?

SELECT total, (select my_count from x where .. AND my_count > 0) as XXX
FROM sales

And you're getting an error because you can't use the result of a subquery as a column name.

answered Apr 02 '12 at 00:10

rgafonso

I have several columns from subqueries, basically orders in last 1, 3, 6 months, wanted to do some filtering based on results – Brettski Apr 02 '12 at 00:17
But you are not joining anything with the main query. Can you provide a better example? – rgafonso Apr 02 '12 at 00:47
This query will return `total, null` if `my_count <= 0`, and will not filter the record. I believe in `..` part of the sub-query, he is doing something with `sales` table. – Amir Pashazadeh Apr 02 '12 at 19:12

2 Answers2