counting with distinct

[room24]

select count(*) from (select distinct certificate_key from independent_investment_certificate_temp)

when i run the above i get this error

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.

 

[hmckillop]

try

select count(distinct (certificate_key )) from independent_investment_certificate_temp

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

When you use a select statement as a derived table it must have an alias.

select count(*) from (select distinct certificate_key from independent_investment_certificate_temp) a

Questions about posting. See faq183-874

 

[donutman]

The suggested code is preferred (although the ( ) around certificate_key isn't necessary), but the error was due to the fact that you didn't alias your derived table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[hmckillop]

I'm confused, does my original code not do exactly what the same, rather than using a derived table?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[donutman]

Your code does the same, but it does use a derived table. You just forgot to give it a name. I think the code suggested by hmckillop will be more efficient, but maybe the optimizer treats them the same.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]