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:

SELECT
    year, AVG(carbon) AS AVG_carbon,
    --    carbon as CompanyCarbon, <--my not working attempt
FROM
    "company"."carbon" c 
WHERE
    LOWER(c.ticker) IN (SELECT LOWER(g4.ticker)
                        FROM "company"."General" g4
                        WHERE industry = (SELECT industry 
                                          FROM "company"."General" g3 
                                          WHERE LOWER(g3.ticker) = 'ibm.us')) 
GROUP BY 
    c.year
ORDER BY 
    year ASC;

The current result is:

year    avg_carbon
--------------------------------
1998    7909.0000000000000000
1999    19465.500000000000
2000    19478.000000000000
2001    182679.274509803922
2002    179821.156862745098

My desired output is:

year    avg_carbon.             Carbon
---------------------------------------
1998    7909.0000000000000000   343
1999    19465.500000000000      544
2000    19478.000000000000      653
2001    182679.274509803922     654
2002    179821.156862745098     644

(adding the carbon column based on “IBM” carbon

Here’s my Carbon table:

ticker  year    carbon
-----------------------
hurn.us 2016    6282
hurn.us 2015    6549
hurn.us 2014    5897
hurn.us 2013    5300
hurn.us 2012    5340
ibm.us  2019    1496520
ibm.us  2018    1438365

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.

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.

select year, AVG(carbon) AS AVG_carbon,
max(case when lower(ticker)  = 'ibm.us' then carbon else 0 end) as CompanyCarbon
from "company"."carbon" c 
GROUP BY c.year
order by year ASC;

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:

select year, AVG_carbon, CompanyCarbon
from 
(
    select year, AVG(carbon) AS AVG_carbon,
    max(case when lower(ticker)  = 'ibm.us' then carbon else 0 end) as   CompanyCarbon
    from "company"."carbon" c 
    GROUP BY c.year
    order by year ASC;
)t where carbon > 0