How exactly does HAVING work with GROUP BY?-CodePudding

As with most programmers, I want to be efficient in my code. In this case, I want everything in 1 statement rather than broken up into many parts.

I am writing a query that provides the highest carbon footprint of each industry group from the most recent year. I'm providing how many companies are in each industry group and grouped by the industry group and year, too.

I want to set MAX(year) in HAVING so that I have the most recent year, but is it possible to do so?

This is what I coded:

SELECT industry_group,
    COUNT(company) AS count_industry,
    ROUND(SUM(carbon_footprint_pcf), 1) AS total_industry_footprint
FROM product_emissions
GROUP BY industry_group, year
HAVING MAX(year) = year
ORDER BY total_industry_footprint DESC
LIMIT 10;

This code provides the industry groups, the count of companies in each industry group, and the carbon footprint of each industry group. HAVING MAX(year) = year doesn't do anything; I still get the highest carbon footprint in descending order, but it's not by the most recent year.

The correct code is:

SELECT industry_group,
    COUNT(company) AS count_industry,
    ROUND(SUM(carbon_footprint_pcf), 1) AS total_industry_footprint
FROM product_emissions
GROUP BY industry_group, year
HAVING year = 2017
ORDER BY total_industry_footprint DESC;

Any suggestions?

CodePudding user response：

this has nothing to do with grouping. You need to filter the year.

SELECT industry_group,
    COUNT(company) AS count_industry,
    ROUND(SUM(carbon_footprint_pcf), 1) AS total_industry_footprint
FROM product_emissions
WHERE YEAR = (SELECT MAX(YEAR) FROM product_emissions)
GROUP BY industry_group
ORDER BY total_industry_footprint DESC
LIMIT 10;

If you need to filter based on industry groups then

SELECT industry_group,
    COUNT(company) AS count_industry,
    ROUND(SUM(carbon_footprint_pcf), 1) AS total_industry_footprint
FROM product_emissions pe
WHERE YEAR = (SELECT MAX(YEAR) FROM product_emissions pei where pe.industry_group = pei.industry_group )
GROUP BY industry_group
ORDER BY total_industry_footprint DESC
LIMIT 10;