We help IT Professionals succeed at work.

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

106 Views
Last Modified: 2016-08-21
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)
Comment
Watch Question

SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.