Оператор select с использованием агрегатных выражений

SELECT, который использует агрегатные функции и содержит группировку (GROUP BY и HAVING):

SELECT 'How many?' =

CASE COUNT(a.Full_Name) -- how can we use IF-ELSE or "()?():()" statement instead of this?

WHEN 1 THEN CONVERT(nvarchar, COUNT(a.Full_Name)) + ' guy'

ELSE CONVERT(nvarchar, COUNT(a.Full_Name)) + ' guys'

END,

'Account name' = t.Name + 's', t.[Description]

FROM dbo.Account a, dbo.[Type] t

WHERE a.Account_Type = t.Id

GROUP BY t.Name, t.[Description] -- question. How can we optimise that so that we don't have to put here every output expression

HAVING t.Name IN ('Teacher', 'Student'); -- how to use a number of column instead of t.Name?

SELECT TOP(1) 'Created questions' = COUNT(q.Id), a.Full_Name

FROM dbo.Account a, dbo.TestCase t, dbo.Question q

WHERE a.Id = t.Id_Creator AND t.Id = q.Id_Test

GROUP BY a.Id, a.Full_Name -- I need to goup only by a.Id but i'm forced to write a.Full_Name. I don't wanna group by Full_Name, 'cause it can be a lotta equal names. How can I avoid this?

ORDER BY 1 DESC;

SELECT MAX(q.Name) as 'The longest question', t.Name, a.Full_Name -- max means by alphabet but not by its length

FROM dbo.TestCase t, dbo.Question q, dbo.Account a

WHERE t.Id = q.Id_Test AND a.Id = t.Id_Creator

GROUP BY t.Id, t.Name, a.Full_Name; -- the same situation as in the previous requests