Counting DISTINCT values in T-SQL

Question

I am trying to count the number of distinct email addresses in a view, but I can't figure out the syntax... Here's what I have:

BEGIN
  SELECT COUNT(*) AS UserCount FROM (
    SELECT DISTINCT EmailAddress 
    FROM viewCohortsAuthorizedByContractor
    WHERE (ListAccess = 1) AND (ContractorId = @id)
  )
END

The inner select works fine, but if I try adding the COUNT, I get the following error: Msg 156, Level 15, State 1, Procedure rptContractorUsersWithListUserCount, Line 23 [Batch Start Line 15] Incorrect syntax near the keyword 'END'.

I'm sure there's a simple solution, I just can't figure it out...

You forgot to specify an alias for your subquery - SELECT COUNT(*) AS UserCount FROM (...) as Q. The keyword AS isn't necessary - SELECT COUNT(*) AS UserCount FROM (...) Q. In your case SQLServer think that END is your alias. Early SteveB showed is more rightly query for your case. — Sergey Menshov
– Sergey Menshov, Commented Nov 16, 2017 at 4:49
Knew it was something simple. Just didn't think of an alias given that there weren't any clauses at the end (Where, Order By). — PKatona
– PKatona, Commented Nov 16, 2017 at 15:18
The syntax of TSQL requires that you specify an alias for a subquery in any case. — Sergey Menshov
– Sergey Menshov, Commented Nov 16, 2017 at 15:38
I said about subqueries which are located in the FROM-block. — Sergey Menshov
– Sergey Menshov, Commented Nov 16, 2017 at 15:44

SteveB · Accepted Answer · 2017-11-16 00:51:54Z

3

You don't need the subquery. Try this:

SELECT count(DISTINCT EmailAddress) as UserCount
FROM viewCohortsAuthorizedByContractor
WHERE (ListAccess = 1) AND (ContractorId = @id)

edited Nov 16, 2017 at 0:51

answered Nov 16, 2017 at 0:46

SteveB

9247 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jaydip Jadhav · Accepted Answer · 2017-11-16 08:34:27Z

0

BEGIN
  SELECT COUNT(*) AS UserCount FROM (
    SELECT DISTINCT EmailAddress 
    FROM viewCohortsAuthorizedByContractor
    WHERE (ListAccess = 1) AND (ContractorId = @id)
  )A   -----give a name for result set
END

edited Nov 16, 2017 at 8:34

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

answered Nov 16, 2017 at 8:05

venkatesh

1

Collectives™ on Stack Overflow

Counting DISTINCT values in T-SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related