Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 51
  • Last Modified:

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)
0
zimmer9
Asked:
zimmer9
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If you have any aggregates like SUM or COUNT then you need a GROUP BY clause for the non-aggregated columns.  This also means that you can lose the DISTINCT.

SELECT
   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
GROUP BY 
   amt1, 
   DATENAME(month, datefile) + ' ' + CAST(YEAR(dateFile) as char(4)), 
   branch

Open in new window


Check out SQL Server GROUP BY Solutions for more tips.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now