Count Group by

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I need help I am trying to do a query for evaluations. I need to be able to
count how many times a number is appears. This is the SQL that I have

SELECT IIf(Course.Q1=0,"0",
IIf(Course.Q1=1,"1",
IIf(Course.Q1=2,"2",
IIf(Course.Q1=3,"3",
IIf(Course.Q1=4,"4",
IIf(Course.Q1=5,"5",
IIf(Course.Q1=6,"6"))))))) AS Q1Range,
Count(Course.Q1) AS CountOfQ1
FROM Course
GROUP BY IIf(Course.Q1=0,"0",
IIf(Course.Q1=1,"1",IIf(Course.Q1=2,"2"
,IIf(Course.Q1=3,"3",IIf(Course.Q1=4,"4",
IIf(Course.Q1=5,"5",IIf(Course.Q1=0,"6")))))));

It keeps giving this error
You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)
 
Why not just use

SELECT Course.Q1 as Q1Range, Count(Course.Q1) as CountQ1
FROM Course
WHERE Course.Q1 Between 0 and 6
GROUP By Course.Q1

If you need to force the value of Q1 to be a text value, you can wrap it in
CText function or just use
Course.Q1 & ""
 
Back
Top