Huzaifa M Aamir Huzaifa M Aamir - 1 year ago 64
SQL Question

Count occurrences of a column with alias in SQL

I am trying to get a count of names and the id's associated with it
for example
Name : Jon Smith
cnt : 2
Id : 1

And the second John Smith record will have a different ID associated with it.

I have looked at other suggested solutions and have gotten this far:

select, concat(a.firstname,' ',a.lastname) as name, b.cnt
from table a
join (select concat(firstname,' ',lastname) as bname, count(*) as cnt
from table by firstname,lastname) b

However, sql is giving an error saying invalid column name 'name'.
I know I can get the desired results just using one column to get the counts and then do an subquery for the remaining columns to be placed back in. But I need it for the names

Answer Source

You cannot use the alias in the JOIN condition. You can either put it in a subquery first, or use the concatenated columns as this:

    CONCAT(a.firstname, ' ', a.lastname) AS name,
FROM table a
        CONCAT(firstname, ' ', lastname) AS bname,
        COUNT(*) AS cnt
FROM table b
GROUP BY firstname, lastname
) b
    ON CONCAT(a.firstname, ' ', a.lastname) = b.bname