sql server - How can i nest these SQL statements without a group by clause error? -

i trying query database has these params:

transaction date, user email address

what have done use query:

select [user email address],  count(*) 'count'           [database].[table]     group [user email address] 

which displays table params:

email address, count

in case count column shows number of occurrences of user email in original table.

what trying next @ transaction date column last year today, , compare count column subset count column of orginal (which goes 3 years). specifically, want end resultant table be:

user user email address, countdiff

where countdiff difference in counts 1 year subset , original subset.

i have tried:

select [user email address], [transaction date], [count - count(*)] 'countddifference'  (      select [user email address],  count(*) 'count'           [database].[table]     group [user email address]   )  a.[transaction date] >= '2011-08-07 00:00:00.000'   order [count] desc 

but error [transaction date] not in group clause or aggregate. if put in group next [user email address], messes data.

this common problem i've had. ways circumvent this?

you need use 2 different subqueries: 1 counts full entries , 1 counts entries of last year.

maybe you:

select a.*, a.[count] - coalesce(b.[count], 0) 'countdif'       (         select [user email address],  count(*) 'count'         [database].[table]         group [user email address]     )     left join (         select [user email address],  count(*) 'count'         [database].[table]         [transaction date] >= '2011-08-07 00:00:00.000'         group [user email address]     ) b on a.[user email address] = b.[user email address]