Grouping by a function

[Corvu]

Hi everyone,
I work at a hospital and I need to aggregate data into months
I am trying to Group some data by a month and year function, the code is below
However the database wont let me, it keeps telling me I need to name my coloums, Ive tried doing this about 10 different ways with no luck eg:
ORDER BY
a.SCV_DATE or
Month or
{fn MONTHNAME(a.SCV_DATE)} AS Month
Has anyone got any ideas, I will be eternally grateful

SELECT
c.SUR_NAME,
{fn MONTHNAME(a.SCV_DATE)} AS Month,
{fn YEAR(a.SCV_DATE)} As Year,
COUNT(*),
COUNT(*)
FROM
odbc.dbo.PATIENT d,
odbc.dbo.SESSION b,
odbc.dbo.SURGEON c,
odbc.dbo.SESCORVU a
WHERE
c.SUR_TYPE = "C" AND
a.SCV_DATE = d.PAT_DATE AND
a.SCV_SESSNO = d.PAT_SESSNO AND
a.SCV_THEATRE = d.PAT_THEATRE AND
b.SES_DATE = d.PAT_DATE AND
b.SES_SESSNO = d.PAT_SESSNO AND
b.SES_THEATRE = d.PAT_THEATRE AND
c.SUR_CODE = b.SES_CONSULTANT AND
( a.SCV_DATE >= '&"Start Date"' AND
a.SCV_DATE <= '&&quot;End Date&quot;') AND
a.SCV_THEATRE LIKE &quot;DU%&quot; AND
b.SES_SPECL IN (&quot;GYN&quot;, &quot;OBGY&quot AND
d.PAT_CANCELLED = &quot;N&quot;
GROUP BY
c.SUR_NAME,
a.SCV_DATE


Thanks for any help you can give me

 

[rac2]

The GROUP BY statement has a restriction on what can be mentioned in the SELECT clause. A column in the SELECT clause must be used in the GROUP BY clause.

Your statement has 4 or 5 columns/expressions in the SELECT and only one of them is used to GROUP BY, so that is against the rules.

Add the two expressions to the GROUP BY and it should work.

GROUP BY c.SUR_NAME,
{fn MONTHNAME(a.SCV_DATE)},
 {fn YEAR(a.SCV_DATE)}

Do not use the aliases in the GROUP BY.

I don't know what the {fn } notation means, but I left it in on the assumption that your RDBMS understands it.

 

[Corvu]

Thanks Rac2 for taking the time to answer my question, I tried your suggestion and unfortunately it didnt work, the error message told me &quot;Name expected&quot;. I wrote the code

GROUP BY
c.SUR_NAME,
{fn MONTHNAME(a.SCV_DATE)},
{fn MONTHNAME(a.SCV_DATE)}

and also

GROUP BY
c.SUR_NAME,
MONTHNAME(a.SCV_DATE),
MONTHNAME(a.SCV_DATE)

which returned the error message of &quot;Superfluous&quot;

Any ideas?, if not thanks for your time