Home > Is Not > It Is Not Contained In Either An Aggregate Function Or The Group By Clause.

It Is Not Contained In Either An Aggregate Function Or The Group By Clause.

Contents

It is better to use query paramters in case of concatenating multiple strings. asked 2 years ago viewed 19039 times active 2 years ago Linked 123 Reason for Column is invalid in the select list because it is not contained in either an aggregate That's fundamental to relational theory too. –Bill Karwin Dec 22 '12 at 16:45 8 Love it when folks take the time to explain using first principles. I am trying to write a query to fetch the list of distinct employee record by the first swipe for today.

If you don't need UID and isDefault, you need to delete them from the select clause. All rights reserved.Terms of Use|Trademarks|Privacy Statement|Site Feedback more hot questions question feed lang-sql about us tour help blog chat data legal privacy policy work here advertising info mobile contact us feedback Technology Life / Arts Culture / Recreation For every expert, there is an equal and opposite expert. - Becker's Law My blog hai mam, the code returns some values... http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e

It Is Not Contained In Either An Aggregate Function Or The Group By Clause.

sql group-by share|improve this question edited Aug 12 '13 at 12:52 Bill Karwin 292k53406583 asked Dec 22 '12 at 3:31 david blaine 1,23752245 marked as duplicate by mu is too short, How to make use of Devel debugging functions on large or complex objects Why are copper cables round? below isthe table now Alert (AID,SMS,isDefault)eg. 1, 98765432, N ...... 100, 23456789, N --> users adds new alert. more stack exchange communities company blog Stack Exchange Inbox Reputation and Badges sign up log in tour help Tour Start here for a quick overview of the site Help Center Detailed

  • command = new SqlCommand("SELECT MAX(AID), UID, isDefault FROM Alert where (UID =" + uid + ") AND (isDefault='" + isdefault + "')", dc.Connection); above is my code when i try on
  • Thanks.
  • Detect ASCII-art windows made of M and S characters Archeological evidence of nuclear warfare How does changing metrics help to find solutions to a partial differential equation?
  • There are three possibilities in each case, and nothing in the query makes it clear which value to choose for b in each group.
  • Tell me if its ok - To group all employees that work in the same location we have to first mention the LocationID.
  • There are three possibilities in each case, and nothing in the query makes it clear which value to choose for b in each group.
  • What is this blue thing in a photograph of a bright light?
  • How can I stop Alexa from ordering things if it hears a voice on TV?
  • How to explain extreme human dimorphism?
  • Boyfriend is coowner with sister, wants to move out Why isn't the religion of R'hllor, The Lord of Light, dominant?

select distinct [employee number], [Employee First Name] ,[Employee Last Name] ,min([DateTime]) ,[Card Number] ,[Reader Name] ,[Status] ,[Location] from [Interface].[dbo].[VwEmpSwipeDetail] group by [employee number] where [datetime] = CURDATE(); Getting error: Column 'Interface.dbo.VwEmpSwipeDetail.Employee Output N in base -10 What is a non-vulgar synonym for this swear word meaning "an enormous amount"? All I want to do is join the tables and then group all the employees in a particular location together. Mssql Group By Is it possible to set a composite NOT NULL constraint in PostgreSQL Why doesn't my piece of code work?

something like STUFF(( SELECT (' - '+ColName) AS ColName .... What does Joker “with TM” mean in the Deck of Many Things? dev.mysql.com/doc/refman/5.5/en/… But in standard SQL, each column should contain only one value. Generalization of winding number to higher dimensions Does every data type just boil down to nodes with pointers?

Why doesn't SQL put b1,b2,b3 all together in that column ? –david blaine Dec 22 '12 at 4:51 9 @davidblaine, MySQL has a function GROUP_CONCAT() for that. Incorrect Syntax Near The Keyword 'group'. This is not how group by works. Any help please? select Cd.StockId ,Cd.CounterId ,IT.CId ,C.Name ,IT.ItemName ,Cd.OpeningUnits ,Cd.OpeningStock ,(dtp.Unit+dtRet.Unit+dtRec.Unit+dtExcess.Unit) as RecUnit ,(dtp.Wgt+dtRet.Wgt+dtRec.Wgt+dtExcess.Wgt) as RecWgt ,(dtSale.Unit+dtIss.Unit+dtShort.Unit) as IssUnit,(dtSale.Wgt+dtIss.Wgt+dtShort.Wgt) as IssWgt ,(Cd.OpeningUnits+(dtp.Unit+dtRet.Unit+dtRec.Unit+dtExcess.Unit)-(dtSale.Unit+dtIss.Unit+dtShort.Unit))
as ClsUnit ,(Cd.OpeningStock+(dtp.Wgt+dtRet.Wgt+dtRec.Wgt+dtExcess.Wgt)-(dtSale.Wgt+dtIss.Wgt+dtShort.Wgt))
as ClsWgt ,C.ShortName from tblCounterDetails Cd join

Select List Is Not In Group By Clause And Contains Non Aggregated Column

problem here that might arise is that, if 10 users add new alerts into the table at the same time, the AID that i will be getting back from the command http://stackoverflow.com/questions/6456727/column-is-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-ag When you use group by you have to select the thing specifically that you are grouping –Alex W Dec 22 '12 at 3:34 @AlexW - please have a look It Is Not Contained In Either An Aggregate Function Or The Group By Clause. asked 3 years ago viewed 101629 times active 1 year ago Linked 0 The following SQL code throws error 123 Reason for Column is invalid in the select list because it Mssql Aggregate Functions Sunday, October 31, 2010 7:25 PM Reply | Quote 0 Sign in to vote Hi, When you use group by, each column in the select clause must be either defined via

Is it a security vulnerability if the addresses of university students are exposed? asked 5 years ago viewed 25179 times active 5 years ago Linked 7 Efficiently Include Column not in Group By of SQL Query Related 2invalid in the select list because it Not the answer you're looking for? Try: select distinct [employee number] ,[Employee First Name] ,[Employee Last Name] ,min([DateTime]) ,[Card Number] ,min([Reader Name]) from [Interface].[dbo].[VwEmpSwipeDetail] where CAST([datetime] AS DATE)=CAST(GETDATE() AS DATE) group by [employee number], [Employee First Name], Operand Data Type Bit Is Invalid For Max Operator.

Also, don't forget to VOTE UP if you find a post helpful. So, this explains the "it is not contained in either an aggregate function" part of the error. Why do the physical properties of an egg shell change when the egg shell is exposed to vinegar for a week? share|improve this answer answered Jun 18 '13 at 9:45 Thomas Bovee 52143 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google

How to turn on Xbox One from Windows 10 PC using Cortana Are people of Nordic Nations "happier, healthier" with "a higher standard of living overall than Americans"? Cannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery. All I want to do is join the tables and then group all the employees in a particular location together. Can time travel make us rich through trading, and is this a problem?

The SQL standard requires the GROUP BY to be populated (of mainstream RDBMS, only MySQL doesn't and makes a guess to resolved the ambiguity).

If you remove group by, you'll get a lot of trash in result query (all columns from baptism, sponsors and sub-selects) share|improve this answer answered Jun 18 '13 at 7:55 P0ZiTR0N Compiling multiple LaTeX files more hot questions question feed lang-sql about us tour help blog chat data legal privacy policy work here advertising info mobile contact us feedback Technology Life / When you use group by you have to select the thing specifically that you are grouping –Alex W Dec 22 '12 at 3:34 @AlexW - please have a look Each Group By Expression Must Contain At Least One Column That Is Not An Outer Reference. Generalization of winding number to higher dimensions Why the windows of ships bridges are always inclined?

makes sense ? –david blaine Dec 22 '12 at 4:20 3 then replace the first line of your original example with: SELECT loc.LocationID, COUNT(*) –ExactaBox Dec 22 '12 at 4:22 Did Joseph Smith “translate the Book of Mormon”? Browse other questions tagged sql-server group-by or ask your own question. Different tasks, same characters What do you call this alternating melodic pattern?

share|improve this answer answered Dec 22 '12 at 3:54 Alex W 21.4k65071 add a comment| Not the answer you're looking for? Cost effective drivetrain maintanance No word for "time" until 1871? Concatenating strings is not optimal since for each concatenation a new string is created. What does Joker “with TM” mean in the Deck of Many Things?

How does changing metrics help to find solutions to a partial differential equation? SQL Server > Transact-SQL Question 0 Sign in to vote im trying to do a simple select statement across sql server express. Removing wheel housing liner - Understanding instructions Ultimate Australian Canal more hot questions question feed lang-sql about us tour help blog chat data legal privacy policy work here advertising info mobile Browse other questions tagged sql-server sql-server-2008 select or ask your own question.

asked 3 years ago viewed 5067 times active 3 years ago Visit Chat Related 2invalid in the select list because it is not contained in either an aggregate function or the In that case, lose the GROUP BY statement.