hi,

i have a table 'Details' with columns [id int; level varchar(20)]. i want to get two things from one single query:
1) the total number of items with Details.id=xxx
2) all the associated [level] text

i tried a few times doing things like:
SELECT [Level], COUNT(*) FROM Details WHERE id=xxx GROUP BY id

but it either gives me error "Column [Level] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

i am using MS SQL Server as the back engine and T-SQL in Access...

Help~

thanks in advance

Perhaps you can do this:

select level, (select count(*) from details where id=xxx) as cnt
from details where id=xxx;

Works in Oracle, I don't know about your DBMS.

Beautifully done~ thanks!