Click here to Skip to main content
Rate this: bad
good
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
Edited 12-Nov-12 8:30am
Nelek49.3K
v2
Comments
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
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
0 OriginalGriff 210
1 Richard MacCutchan 100
2 kbrandwijk 90
3 ProgramFOX 80
4 Mukesh Bhagat 65
0 Sergey Alexandrovich Kryukov 9,050
1 OriginalGriff 8,151
2 CPallini 2,613
3 Richard MacCutchan 2,221
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 22 Nov 2012
Copyright © CodeProject, 1999-2014
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