Sum does not work in my select statement

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

Guest

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
 
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'
 
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.

Ofer said:
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'

DSTDIVA3 said:
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
Click to expand...
Click to expand...
 
Back
Top