Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

I am using SQL Server 2008 and I am stuck on a group by clause.

I am using SQL Server 2008 and I am stuck on a group by clause as follows:

I have a table titled tbl_CSL_ImportMonthlyChecksEntity with sample records as follows:

amt1        amt2          DateFile         Branch    
1               2058.92     8/31/2012      AP
1               1500.00     8/31/2012      AP
1                   49.00   12/31/2010      OTHER
1                 800.00   12/31/2010      OTHER
1                 371.28   12/31/2010      OTHER

My objective is to get a result set as follows:

amt1     DateFile_By_Month        Branch     Sum_Of_amt2     Count_of_Import_Monthly_Checks_Entity
1            August 2012                    AP             3,558.92               2
1            December 2010              OTHER     1,220.28               3

I used the following query:

SELECT DISTINCT
      amt1,
      DATENAME(month, datefile) + ' ' + CAST(YEAR(dateFile) as char(4)) as DateFile_By_Month,
      branch,
      Sum(amt2) AS count_amt2,
      Count(*) AS Count_of_Import_Monthly_Checks_Entity
FROM tbl_CSL_ImportMonthlyChecksEntity

but I get the following error:

Msg 8120, Level 16, State 1, Line 2
Column 'tbl_CSL_ImportMonthlyChecksEntity.amt1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

the table layout is as follows:

tbl_CSL_ImportMonthlyChecksEntity

bankname VARCHAR(53),
banknumber VARCHAR(19),
amt1 VARCHAR(6),
amt2 NUMERIC(15,2),
datefile datetime,
dateimported datetime,
importedby VARCHAR(50),
branch VARCHAR(50)
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial