Can I use lower on a nested query?

It's been a while since I used SQL but getting my hands dirty again. I'm getting a error while trying to lowercase a list of strings.

I have two queries that I want to turn into one, and each works on their own:

    select ticker from "General" g4 where industry = (select industry from "General" g3 where g3.ticker = 'IBM.US');
ticker
NSIT.US
SNX.US
NRILY.US
WIT.US

This returns a list of tickers based on the industry of the ticker variable(in this case all companies in the same industry as IBM)

then I want to get the average revenue for each company in that year:

select year, AVG(revenue) AS AVGrevenue  from revenue r where lower(ticker) = lower('ibm.us') GROUP BY c.year;
works awesome:

2013    2750040.000000000000
2014    3675122.000000000000
2015    3455017.000000000000
2016    2140280.000000000000
2017    3401381.000000000000

but when I combine it, it does not work:

Error occurred during SQL query execution

select year, AVG(revenue) AS AVGrevenue  
from revenue r 
where lower(ticker) = lower(

select ticker from "General" g4 where industry = (select industry from "General" g3 where g3.ticker = 'IBM.US')

) 
GROUP BY r.year;

I get this error:

Reason:
SQL Error [42601]: ERROR: syntax error at or near "select"
  Position: 635

What am I doing wrong ?

Solution

You could, but I would suggest moving the lower() into the query:

where lower(ticker) = (select lower(g4.ticker)
                       from "General" g4
                       where industry = (select industry from "General" g3 where g3.ticker = 'IBM.US')

                       )

If you wanted the subquery to be an argument to lower(), that is also allowed by you need an extra level of parentheses:

where lower(ticker) = lower( (select g4.ticker
                              from "General" g4
                              where industry = (select industry from "General" g3 where g3.ticker = 'IBM.US')

                             ) 
                           )

One set of parentheses is for the function call. The second is for the correlated subquery.