SSRS 2005 - Using Group By question
Hi all I am trying to display montlhy totals for a table. I have the following: SELECT DATAAREAID, ACCOUNTNUM, TRANSDATE, sum(AMOUNTMST) as Amount, DIMENSION AS Department, DIMENSION2_ AS CostCentre FROM LEDGERTRANS WHERE (DATAAREAID IN (@CompanyID)) AND (ACCOUNTNUM IN (@GLAccount)) AND (DIMENSION in (@Department)) And (TRANSDATE >= '01/11/'+cast(YEAR(@Date) as varchar(4))) and (TRANSDATE <=(@Date)) Group by dataareaid,accountnum, month(TransDate),dimension, DIMENSION2_ Order by accountnum, dimension, DIMENSION2_ However, when I try to run this I get an error saying: Column 'LEDGERTRANS.TRANSDATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Am I doing something really silly here?? All help very appreciated, cheers Naz
May 27th, 2011 7:55am

You didn't include the month in the SELECT statement. Try this. SELECT DATAAREAID, ACCOUNTNUM, month(TransDate), sum(AMOUNTMST) as Amount, DIMENSION AS Department, DIMENSION2_ AS CostCentre FROM LEDGERTRANS WHERE (DATAAREAID IN (@CompanyID)) AND (ACCOUNTNUM IN (@GLAccount)) AND (DIMENSION in (@Department)) And (TRANSDATE >= '01/11/'+cast(YEAR(@Date) as varchar(4))) and (TRANSDATE <=(@Date)) Group by dataareaid,accountnum, month(TransDate),dimension, DIMENSION2_ Order by accountnum, dimension, DIMENSION2_
Free Windows Admin Tool Kit Click here and download it now
May 27th, 2011 8:37am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics