count distinct records (all columns) not working

Question

what's proper syntax for

count (distinct *) from t1

I am getting he following error:

[Err] 42000 - [SQL Server]Incorrect syntax near the keyword 'distinct'.

score 49 · Accepted Answer · answered Dec 16 '13 at 20:53

49

select count(*)
from
(
   select distinct * from your_table
) x

answered Dec 16 '13 at 20:53

juergen d

worked, thanks I will accept as soon as ten minutes pass by. Is there a particular reason why this works and the original one didn't, other than its how the syntax is supposed to be ? – Eddie Martinez Dec 16 '13 at 20:59
@EduardoDennis: sql-syntax - select count(*) from table ... and table is select distinct * from innertable – halfbit Dec 16 '13 at 21:01
@EduardoDennis: You need to specify one column when you use `distinct`: `select count(distinct col1) ...` If you want distinct records of all columns you have to use a subquery as I did. – juergen d Dec 16 '13 at 21:09
May be advisable to list out the columns if this is going to be re-used. – sam yi Dec 16 '13 at 21:28
What's the `) x` on line 5 do? – alex Jun 22 '16 at 19:54
1

it names the subquery: `(subquery) alias_for_subquery` – juergen d Jun 22 '16 at 22:56

1 Answers1