SQL Count not getting correct Results.

[Patricha]

Hi,

I am trying to get a distinct count.
My data looks like this...

StudentId ; BookItem ; BookStoreNum ; BookDate
11564;1;342680;12/15/2001
11564;21;342680;12/15/2001
11564;21;342680;12/22/2001
12244;1;342680;1/11/2001
12244;1;342680;2/24/2001

I want a DISTINCT COUNT
Select Count(BookDate) as Books
FROM RecordsData
Where StudentId = '11564'
And BookStoreNum = '342680'
And BookItem In ('1','21');

I want the sql to return Results = '2'
but I keep getting total records Results = '3'
I am using Access97.

I am thinking of a Select within a Select, but can't seem to get it working. Any Help in solviing this is appreciated.

 

[dalchri]

Yes, I think the only way to do this is using a subquery. For example:
[tt]
SELECT COUNT(*) AS DistinctCount
FROM (
SELECT DISTINCT BookItem
FROM RecordsData
WHERE StudentId='11564' And
BookStoreNum='342680' And
BookItem In ('1','21')
)
[/tt]
Note that since you are filtering by StudentId and BookStoreNum, you don't need to include them in the SELECT DISTINCT clause.

 

[Patricha]

Thanks dalchri,

I copied and pasted directly into a blank Query
and I am getting an error when I try this Sql.

Syntax Error in FROM clause

am I missing something?

Thanks

 

[Guest_imported]

Select BookDate, Count(*) as Books
FROM RecordsData
Where StudentId = '11564'
And BookStoreNum = '342680'
And BookItem In ('1','21')
Group By BookDate;

Will return two records. One for each distinct date within the other criteria:
12/15/2001 2
12/22/2001 1

If you want the count to return only 2, you'll need some other criteria.

 

[dalchri]

Patricha,

Make certain that you get that last close parenthesis that is on a line all by itself:
[tt]
SELECT COUNT(*) AS DistinctCount
FROM (
SELECT DISTINCT BookItem
FROM RecordsData
WHERE StudentId='11564' And
BookStoreNum='342680' And
BookItem In ('1','21')
) <-- This guy right here
[/tt]
I get the same error if I omit it.

 

[AccessAnnie]

I'm also trying to do a similar query in Access97 and have tried exactly the response above ensuring that I have enclosed with the ')' and still get:

Syntax error in From clause

Is there anyone who has actually got this to work in Access97?

 

[r937]

pat, you were so close!!

your GROUP BY query will &quot;return two records. One for each distinct date within the other criteria&quot;

&quot;If you want the count to return only 2, you'll need some other criteria&quot;

yes, 2 is the desired answer, the number of distinct dates, which is the number rows produced by your GROUP BY query

access doesn't support COUNT DISTINCT (that would be too easy, eh), so the subquery approach is required

first, isolate the distinct dates in the subquery, then count them with the main query

unfortunately access doesn't support SELECT ... FROM ( SELECT ...) either

but it does offer a nice alternative, querying a saved query

take the subquery (which could have used DISTINCT, but i also prefer the GROUP BY because you get COUNT(*) for free, even though we aren't using it in this situation) and save it as query BookDates_Q --

     select BookDate, count(*)
       from RecordsData
      where StudentId = 11564 
        and BookStoreNum = 342680 
        and BookItem in (1,21)
   group by BookDate

now you can run

     select count(*)
       from BookDates_Q

and you get the answer 2


rudy

 

[cmmrfrds]

Rudy, is this example a derived query?
unfortunately access doesn't support SELECT ... FROM ( SELECT ...) either

 

[r937]

i'm not sure what the proper terminology is for SELECT ... FROM ( SELECT ... )

the query in the FROM clause (i guess that makes it a subquery) is used in place of a table, so perhaps you could call it a derived table...

my strength is not the theory, it is knowing what to do when the theory doesn't work, e.g. when access doesn't support COUNT DISTINCT

 

[cmmrfrds]

It would be nice if Access supported the count distinct, but I have built derived table queries in Access. Here is an example that I used for a max date. Access uses the &quot;[ ].&quot; syntax.

SELECT dt.Item, dt.maxdatesold, A.vendor
FROM tblSales AS A INNER JOIN [Select b.item, Max(B.DateSold) AS MaxDateSold from tblSales as B
Group by b.item]. AS dt ON dt.Item = A.Item
WHERE maxdatesold = A.datesold;

 

[r937]

whoa, [ ]. is new to me

thanks a lot!!

when did that come in? it doesn't work in access 97


rudy

 

[cmmrfrds]

I have not tried it in Access 97, but it does work in Access 2000. It is so easy sometimes to think something is going to work and then find out that a particular database or version does not support the capability. This happens to me between Access and SQL Server syntax also.

Thanks for the heads up on Access 97.