COUNT with Select Distinct on an Expression

[Sketchin]

Hi All, I have the following SQL which returns rows of distinct numbers that are calculated from a field.

SELECT Distinct (Left([ProjectID],4)) AS NumberOfCalls
FROM tblProject;


ProjectID looks something like this : 1307-IND-001 and NumberOfCalls looks like : 1307

I need to count the number of unique "NumberOfCalls" entries that there are in a list of about 50 rows. Currently the SQL returns a list of numbers like this:

1307
1311
1401
etc...

I just want NumberOfCalls to show "3". Is there an easy way to do this?

 

[pbaldy]

Shot in the dark:

SELECT Left([ProjectID],4) AS Project, Count(*) AS NumberOfCalls
FROM tblProject
GROUP BY Left([ProjectID],4)

 

[Sketchin]

This shows me a count for each row. For example, I might have 10 "1307" entries, but I only want it counted as 1.

 

[pbaldy]

So you basically want the number of records returned by your query? This feels like a kludge, but should work:

SELECT Count(*) As HowMany
FROM (SELECT Distinct Left([ProjectID],4) AS NumberOfCalls FROM tblProject) As Alias

 

[Sketchin]

That worked perfect, many thanks!

 

[pbaldy]

No problemo!