Click here to Skip to main content
12,501,692 members (63,966 online)
Rate this:
 
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:

select
    [FailureModels].[Id],
    [FailureModels].[Description],
    [FailureModels].[FailureRate],
    [FailureModels].[FailureRateDistribution],
   [FailureModels].[ModelType],
    iif([FailureModels].[ModelType] = 'Rate&', [FailureModels].[RepairRate], [FailureModels].[MTTR]) as RepairRateMTTR,
   [GateGroupImportance].[FVImp],
   [GateGroupImportance].[BBImp],
   [GateGroupImportance].[Project],
   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,
Thanks,
Stephen.
[edit]code block added[/edit]
Posted 12-Nov-12 6:38am
Updated 12-Nov-12 8:30am
Nelek101.6K
v2
Comments
snorkie 12-Nov-12 15:25pm
   
Have you tried counting a column instead? Try COUNT([FailureModels].[Id])
digimanus 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 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 13-Nov-12 3:57am
   
indeed it is the group by that is missing
Kschuler 13-Nov-12 10:58am
   
I agree. You should post this as a solution, not just a comment.

1 solution

Rate this: bad
 
good
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,
Stephen.
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160919.1 | Last Updated 22 Nov 2012
Copyright © CodeProject, 1999-2016
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