sql for grouping and counts

[TheShabz]

Hey guys, in need of a bit of help.

I have a table containing enrollments of apartment tenants for a particular service. our account numbers, however, are not for the tenants, but for the building itself. so when a tenant enrolls, we have a field for their unit that we put in with the building's acct number.

Now, what I need to do is get the count of account numbers to guage how many enrollments in that building. however, i want each unit to only be counted once (they may have created multiple enrollments because one failed, or whatever)

so basically, a shortened version of the table would be such

acctNum, EnrollNum, AptNum


123, asv, 1
123, aer, 2
123, set, 3
123, eth, 3

i want the count for acctnum 123 to be 3

note, there will be other acctnums in the table.

[ajetrumpet]

why not use stacked queries?

select distinctrow acctnum, aptnum

from table

select acctnum, count("*") from query

group by acctnum

[TheShabz]

I was kind of hoping to do it all in one shot because it would be updating another field with the counts. It just means breaking down my query into a few more. I wanted a cleaner workflow but i guess this looks more impressive anyway. Looks like I'll get to break 40 queries on this. -_-