LIMITED TIME OFFER: 50% OFF OF PREMIUM WITH OUR ANNUAL PLAN (THAT'S $294 IN SAVINGS).
GET OFFER

Using Alias within Function : 254-6

Screen Link:
Group Summary Statistics In SQL | Dataquest

My Code:

SELECT Major_category, SUM(Women) AS Total_women, AVG(ShareWomen) AS Mean_women,
SUM(Total) * Mean_women AS Estimate_women
FROM recent_grads
GROUP BY Major_category;

I expected code to execute however I receive an error. When I replace “Mean_women” by AVG(ShareWomen) in the following line of code, it works.

SUM(Total) * AVG(ShareWomen) AS Estimate_women

My query is why is the code not working using the alias defined previously ?

You have defined it, but that doesn’t imply that the particular SQL variant has executed it as well.

The column aliases are “accessible” after the SELECT clause has been executed (usually).

It is possible a different SQL variant allows for something like this, but this one doesn’t.

SQL, in general, is just a weird language as per me. Because while yours throws an error, if I modified it so that the aliases are in quotes -

SELECT Major_category, SUM(Women) AS "Total_women",
        AVG(ShareWomen) AS "Mean_women", SUM(Total)*"Mean_women" AS "Estimate_women"
FROM recent_grads
GROUP BY Major_category;

The above won’t throw an error, it will create the Estimate_women column, but all the values in that column will be 0. And I have no idea why.

1 Like

Understood. SQL quirks. Thanks.