Solved

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

Posted on 2016-08-21
1
28 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now