subquery in SQL select statement

[dolodolo]

I’m trying to run the following script and get an error (see below):

select
ach.hperson,
ach.sname,
ach.sacct,
ach.bportal,
ach.hroommate
FROM
(select hperson, count(hmy), brecur
from achdata
where brecur <> 0
group by hperson, brecur
having count(hmy) > 1) main
inner join achdata ach on ach.hperson = main.hperson

Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'main'.

Please help,
Thanks

 

[RiverGuy]

You need to create a name for your count column.

For example: count(hmy) As hmy_count

 

[dolodolo]

Thanks mucho!