How to Select from more columns but group by 1 column?

SELECT        studentnum
FROM            Atten
WHERE        (att = 'Yes') AND开发者_运维问答 (unitCode = 'MMA1034') 
GROUP BY studentnum
HAVING        (COUNT(*) < 4)

How do i select more columns? eg, student_name as well?

If student information is in Student table, then query may look like this:

SELECT student_name, student_birth_day, studentnum
FROM Student S
RIGHT JOIN (
  SELECT studentnum, count(*) as cnt
  FROM   Attendance
  WHERE (attStatus = 'Yes') 
  AND   (unitCode = 'MMA1034')
  GROUP BY studentnum
  HAVING (COUNT(*) < 4)
) A
ON A.studentnum = S.studentnum

From GROUP BY (Transact-SQL)

Each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list

So you have to include it in the group by if it is unaggregated in the select list.

So if you wish to have student_name in the select list, unaggregated, then you need something like

SELECT  studentnum,
        student_name
FROM    Attendance 
WHERE   (attStatus = 'Yes') 
AND     (unitCode = 'SIT103') 
AND     (CONVERT(VARCHAR, attDate, 101) < '10/10/2011') 
GROUP BY    studentnum,
            student_name
HAVING  (COUNT(*) < 4)

if the student_name exist in the same table you need to pass it after your studentnum like

SELECT studentnum,student_name

if student_name exist in the different table you need to use the joins.