COUNT(*) v COUNT(column)

SELECT CONCAT(zip,crt) AS zipcr, COUNT(CONCAT(zip,crt)) AS freq FROM zipcr GROUP BY zipcr

works

Then, why not this:

SELECT CONCAT(zip,crt) AS zipcr, COUNT(zipcr) AS freq FROM zipcr GROUP BY zipcr

because you are not allowed to reference a column alias in the same SELECT clause… you can reference it in ORDER BY and GROUP BY, but not in SELECT

you can work around this by pushing the alias down into a subquery –

SELECT zipcr , COUNT(zipcr) AS freq FROM ( SELECT CONCAT(zip,crt) AS zipcr FROM zipcr ) AS subquery GROUP BY zipcr

by the way, your title “COUNT(*) v COUNT(column)” is actually a different issue, and has to do with nulls

Thanks again. I’m always thankful for your expertise.

Niche

What do you mean?

COUNT(*) counts rows returned by the FROM clause (as filtered by the WHERE clause)

COUNT(column) works almost the same, except it counts only non-null values of column

Very good to know. Thanks again.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.