Skip to content
Advertisement

Can I query a aggregated query and a specific row’s query when using subqueries?

I am new to SQL and I wanted to return the results of a specific value and the average of similar values. I have gotten the average part working but I’m not sure how to do the specific value part.

For more context, I have a list of carbon emissions by companies. I wanted the average of a industry based on a company’s industry(working perfectly below), but I am not sure how to add the specific companies info.

Here’s my query:

The current result is:

My desired output is:

(adding the carbon column based on “IBM” carbon

Here’s my Carbon table:

Based on my limited knowledge, I think my where the statement is causing the problem. Right now I took at a company, get a list of tickers/identifiers of the same industry then create an average for each year.

I tried to just call the carbon column but I think because it’s processing the list of tickers, it’s not outputting the result I want.

What can I do? Also if I’m making any other mistakes you see above please let me know.

Advertisement

Answer

Sample data nd output do not match. So I can’t say for sure but this might be the answer you are looking for.

This will select max(carbon) for any year as CompanyCarbon if lower(ticker) = ‘ibm.us’. Average will be calculated as you did.

To select only rows having positive value in CompanyCarbon column:

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement