Click here to Skip to main content
11,705,212 members (47,042 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL
Hi all,

I'm struggling due to the lack of a count(distinct) function in Jet SQL. My code so far is as follows:

    iif([FailureModels].[ModelType] = 'Rate&', [FailureModels].[RepairRate], [FailureModels].[MTTR]) as RepairRateMTTR,
   count(*) where [FailureModels].[Id] = [PrimaryEvents].[FailureModels]
   from ([FailureModels] INNER JOIN [GateGroupImportance]
   ON [GateGroupImportance].[EventGroup] = [FailureModels].[Id])
   INNER JOIN [PrimaryEvents] ON [PrimaryEvents].[FailureModel] = [FailureModel].[Id]
It all compiles, except the line beginning count(*) which is throwing a syntax missing exception. Has anyone any ideas?

Very grateful for any advice,
[edit]code block added[/edit]
Posted 12-Nov-12 6:38am
Edited 12-Nov-12 8:30am
snorkie at 12-Nov-12 15:25pm
Have you tried counting a column instead? Try COUNT([FailureModels].[Id])
digimanus at 13-Nov-12 3:59am
nope when using functions like count,sum etc you have to define the GROUP BY so the query tells the server what to count exactly
Andrew Cherednik at 12-Nov-12 16:37pm
I do not believe count(*) where [FailureModels].[Id] = [PrimaryEvents].[FailureModels] is a valid SQL syntax. As the snorkie suggested, use COUNT([FailureModels].[Id]). Do not forget to add GROUP BY statement at the end of your query.
digimanus at 13-Nov-12 3:57am
indeed it is the group by that is missing
Kschuler at 13-Nov-12 10:58am
I agree. You should post this as a solution, not just a comment.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Thanks for all the comments, they were invaluable. The group by statement was the solution. I had to group by all of the columns mentioned to solve the problem.

Thanks again,

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 550
1 OriginalGriff 244
2 ppolymorphe 126
3 CPallini 82
4 F-ES Sitecore 70
0 OriginalGriff 8,988
1 Sergey Alexandrovich Kryukov 8,272
2 CPallini 5,189
3 Maciej Los 4,726
4 Mika Wendelius 3,606

Advertise | Privacy | Mobile
Web03 | 2.8.150819.1 | Last Updated 22 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100