Solved

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

Posted on 2016-08-21
1
31 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)
0
Comment
Question by:zimmer9
1 Comment
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41764533
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question