Sum does not work in my select statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DSTDIVA3 - 27 Jul 2005 22:42 GMT

I am trying to retrieve a recordset from another microsoft database. My
select statement is.....

SELECT DISTINCT([CATEGORY]), MAX([FISCAL YEAR]), SUM([ACTUAL COST]) AS
ACTUAL_COST FROM MTB2001 GROUP BY [CATEGORY] HAVING [FISCAL YEAR]='FY05'

I am getting an error stating "You tried to execute a query that does not
include the specified expression '[FISCAL YEAR]='FY05'' as part of an
aggregate function. I am using ADODB as my connection.

I don't know what to do. Please help

Reply to this Message

Ofer - 27 Jul 2005 23:15 GMT

Try using where instead of having
SELECT DISTINCT([CATEGORY]), MAX([FISCAL YEAR]), SUM([ACTUAL COST]) AS
ACTUAL_COST
FROM MTB2001
Where [FISCAL YEAR]='FY05'
GROUP BY [CATEGORY]

But because you are filtering on FISCAL YEAR there is no need to get the max
of it, so you can use

SELECT DISTINCT [CATEGORY], [FISCAL YEAR], SUM([ACTUAL COST]) AS
ACTUAL_COST FROM MTB2001 GROUP BY [CATEGORY] HAVING [FISCAL YEAR]='FY05'

> I am trying to retrieve a recordset from another microsoft database. My
> select statement is.....
[quoted text clipped - 7 lines]
>
> I don't know what to do. Please help

Reply to this Message

DSTDIVA3 - 28 Jul 2005 14:41 GMT

That didn't work either. I am getting the same error with just the word
"FISCAL YEAR" this time. I am going to try another approach.

> Try using where instead of having
> SELECT DISTINCT([CATEGORY]), MAX([FISCAL YEAR]), SUM([ACTUAL COST]) AS
[quoted text clipped - 20 lines]
> >
> > I don't know what to do. Please help

Reply to this Message