locked
is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. RRS feed

  • Question

  • im trying to do a simple select statement across sql server express. it is able to return me the query when i try on sdk but the a/m error appear when i try on web browser or emulator. btw, im doing a web service with mobile app.

    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 web broswer and emulator.

    SELECT MAX(AID) from Alert where (UID =1) AND (isDefault='n')

    above is my code when i try on sdk (vs.net2005)

    what is wrong? im trying to find the latest entry into the table by searching the largest AID in the table.

    • Moved by VMazur Monday, November 1, 2010 10:48 AM (From:ADO.NET Managed Providers)
    Monday, January 8, 2007 9:11 AM

Answers

  • Hi,

    When you use group by, each column in the select clause must be either defined via an Aggregate function (like MAX, MIN, AVG, ...) or must be included in the group by clause.

    In the selectcommand which is declared in your SQLCommand, you use Max(AID), UID, isDefault. The columns UID and isDefault are not included in the group by clause and are not defined by an aggregate function.

    If you don't need UID and isDefault, you need to delete them from the select clause. If you want to have the maximum AID for each UID, isDefault combination, you need to add the following group by clause:

    SELECT...
    FROM ...
    WHERE ...
    GROUP BY UID, isDefault

    Greetz,

    Geert

     

    Geert Verhoeven
    Consultant @ Ausy Belgium

    My Personal Blog

    • Proposed as answer by Naomi N Monday, November 1, 2010 1:21 PM
    • Marked as answer by Kalman Toth Thursday, November 4, 2010 7:09 AM
    Monday, January 8, 2007 9:27 AM
  • Your code seems to be correct, but I think the intention of this query is to use INNER JOIN and not LEFT JOIN. So, I suggest to change to INNER JOIN. Also, you said you wanted TaskType = 1, but in your query you're testing for TaskType = 0
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Kalman Toth Tuesday, August 21, 2012 4:02 PM
    Thursday, June 2, 2011 12:49 PM

All replies

  • Hi,

    When you use group by, each column in the select clause must be either defined via an Aggregate function (like MAX, MIN, AVG, ...) or must be included in the group by clause.

    In the selectcommand which is declared in your SQLCommand, you use Max(AID), UID, isDefault. The columns UID and isDefault are not included in the group by clause and are not defined by an aggregate function.

    If you don't need UID and isDefault, you need to delete them from the select clause. If you want to have the maximum AID for each UID, isDefault combination, you need to add the following group by clause:

    SELECT...
    FROM ...
    WHERE ...
    GROUP BY UID, isDefault

    Greetz,

    Geert

     

    Geert Verhoeven
    Consultant @ Ausy Belgium

    My Personal Blog

    • Proposed as answer by Naomi N Monday, November 1, 2010 1:21 PM
    • Marked as answer by Kalman Toth Thursday, November 4, 2010 7:09 AM
    Monday, January 8, 2007 9:27 AM
  • im wanting to get the max value for the UID. i did try to leave out the UID and isDefault in the SELECT but it didnt work. it return me an invalid value. thanks for ur reply...
    Monday, January 8, 2007 1:45 PM
  • Hi,

    Did you tried to execute your query in SQL Server ?
    Try to execute the following command: SELECT MAX(AID) from Alert where (UID =1) AND (isDefault='n')
    If this works, please post the code that calls the command since I think it is a problem at the .NET side.

    It is better to use query paramters in case of concatenating multiple strings. Concatenating strings is not optimal since for each concatenation a new string is created. A second issue is that you risk having SQL injection. SQL injection can happen when someone enters a value in one of your parameters that closes the query and performs malicious code.

    Greetz,

    Geert

     

    Geert Verhoeven
    Consultant @ Ausy Belgium

    My Personal Blog

    Monday, January 8, 2007 8:01 PM
  • that works in sql server. but  i have amended the codes that calls the command.. i found a way to work it round which is this line, also i have amended the table

    SELECT AID FROM Alert where (AID = IDENT_CURRENT('Alert'))

    im trying to work the flow this way (whenever a user adds his new alert into Alert, i will immediately call the command to retrieve the last entry which will then be used as a reference), however there is nothing to capture to whom it belongs to except for the AID. In a situation, where they may be multiple users adding alerts, it may be returning the inaccurate AID to me. below is the table now

    Alert (AID,SMS,isDefault)
    eg. 1, 98765432, N
    ...
    ...

    100, 23456789, N --> users adds new alert. and this last entry i want retrieve. 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 above may be the wrong 1. sorry if im long winded. juz wanted to explain my problem detailly

    Friday, January 12, 2007 8:02 AM
  •  

    Hi,

    if we are trying to add the several columns in select statement and only one condition in group by.

    how can we do that ??

     

     

    Shiva

    Wednesday, October 24, 2007 12:25 PM
  • Thanks,

    I have been having the group by problem for a while.

    And your answer is the only one that explain how this joining tables with aggregate function works.

    Sunday, October 31, 2010 7:25 PM
  • Hi,

    When you use group by, each column in the select clause must be either defined via an Aggregate function (like MAX, MIN, AVG, ...) or must be included in the group by clause.

    In the selectcommand which is declared in your SQLCommand, you use Max(AID), UID, isDefault. The columns UID and isDefault are not included in the group by clause and are not defined by an aggregate function.

    If you don't need UID and isDefault, you need to delete them from the select clause. If you want to have the maximum AID for each UID, isDefault combination, you need to add the following group by clause:

    SELECT...
    FROM ...
    WHERE ...
    GROUP BY UID, isDefault

    Greetz,

    Geert

     

    Geert Verhoeven
    Consultant @ Ausy Belgium

    My Personal Blog


    Excelllent, explained very clearly, many thanks
    Friday, April 29, 2011 10:18 AM
  • i'm trying to write a simple sql statement with aggrefate function SUM()

    then after adding this sum statement into my query,the sql reports an error like this....

    "Msg 8120, Level 16, State 1, Line 1
    Column 'tblIssue.TaskType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

     

     

    and my query is

     

     

    SELECT   tblCounterDetails.CounterId AS Counter, tblCounterDetails.StockId AS Stock, ISNULL(SUM(tblIssueDetails.Unit), 0) AS Unit, 
               ISNULL(SUM(tblIssueDetails.GWgt), 0) AS Wgt, tblIssue.TaskType
    FROM     tblCounterDetails LEFT OUTER JOIN
               tblIssueDetails ON tblIssueDetails.CNo = tblCounterDetails.CounterId AND tblIssueDetails.ItemId = tblCounterDetails.ItemId LEFT OUTER JOIN
               tblIssue ON tblIssue.Id = tblIssueDetails.IId 
    AND tblIssue.TaskType = 1
    
    GROUP BY tblCounterDetails.CounterId, tblCounterDetails.StockId
    

    can any one please help me to make this code error free!!!!

    thank you in advance!!!!!!!!


    Seminar Topics For Computer Science 2010
    Wednesday, June 1, 2011 6:08 AM
  • Can you please explain the result you want to achieve? To fix this particular error you need to GROUP based on tblIssue.TaskType also, e.g.

    SELECT  CD.CounterId      AS COUNTER, 
         CD.StockId       AS Stock, 
         ISNULL(SUM(ID.Unit),0) AS Unit, 
         ISNULL(SUM(ID.GWgt),0) AS Wgt, 
         I.TaskType 
    FROM   tblCounterDetails CD 
         LEFT OUTER JOIN tblIssueDetails TD 
          ON TD.CNo = CD.CounterId 
           AND TD.ItemId = CD.ItemId 
         LEFT OUTER JOIN tblIssue I 
          ON I.Id = TD.IId 
           AND I.TaskType = 1 
    GROUP BY CD.CounterId, 
         CD.StockId, 
         I.TaskType
    
    I applied aliases to all your tables in the query.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, June 1, 2011 1:31 PM
  • Can you please explain the result you want to achieve? To fix this particular error you need to GROUP based on tblIssue.TaskType also, e.g.

     

    SELECT CD.CounterId  AS COUNTER, 
      CD.StockId  AS Stock, 
      ISNULL(SUM(ID.Unit),0) AS Unit, 
      ISNULL(SUM(ID.GWgt),0) AS Wgt, 
      I.TaskType 
    FROM tblCounterDetails CD 
      LEFT OUTER JOIN tblIssueDetails TD 
      ON TD.CNo = CD.CounterId 
      AND TD.ItemId = CD.ItemId 
      LEFT OUTER JOIN tblIssue I 
      ON I.Id = TD.IId 
      AND I.TaskType = 1 
    GROUP BY CD.CounterId, 
      CD.StockId, 
      I.TaskType
    
    I applied aliases to all your tables in the query.

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    hai mam,

    the code returns some values...

    and i will explain what i want

     

     

    i have 3 tables names dbo.tblissue,dbo.tblissueDetails,dbo.tblcounterdetails

    i want to retrieve counterid and stockid from the table dbo.tblcounterdetails,

    then unit and Gwgt from table dbo.issuedetails,

    with the condition tasktype=1,which is situated in the table dbo.tblissue

    when i executed the query it will not take the value of task type.......

     

    i think you get the relation between these tables can be identified from the code block.....

     

     

    SELECT CD.CounterId  AS COUNTER, 
      CD.StockId  AS Stock, 
      ISNULL(SUM(TD.Unit),0) AS Unit, 
      ISNULL(SUM(TD.GWgt),0) AS Wgt, 
      I.TaskType 
    FROM tblCounterDetails CD 
      LEFT OUTER JOIN tblIssueDetails TD 
      ON TD.CNo = CD.CounterId 
      AND TD.ItemId = CD.ItemId 
      LEFT OUTER JOIN tblIssue I 
      ON I.Id = TD.IId 
      AND I.TaskType = 0 
    GROUP BY CD.CounterId, 
      CD.StockId, 
      I.TaskType

    in this code the line 

     

     

     LEFT OUTER JOIN tblIssue I 
      ON I.Id = TD.IId 
      AND I.TaskType = 0 

    is not working...it may be the join problem with the third table.....will you please help me to make the code completely working?

     

    thank you in advance..

     

     

     


    Seminar Topics For Computer Science 2010
    Thursday, June 2, 2011 6:27 AM
  • Your code seems to be correct, but I think the intention of this query is to use INNER JOIN and not LEFT JOIN. So, I suggest to change to INNER JOIN. Also, you said you wanted TaskType = 1, but in your query you're testing for TaskType = 0
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Kalman Toth Tuesday, August 21, 2012 4:02 PM
    Thursday, June 2, 2011 12:49 PM
  • thank u Naomi  mam.....

    its working now...but the output is not complete....thanks a lot.....


    Seminar Topics For Computer Science 2010
    Friday, June 3, 2011 4:25 AM
  • i'm facing a new problem now....here is the code block of sql..

    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)) <br/>      as ClsUnit
    	,(Cd.OpeningStock+(dtp.Wgt+dtRet.Wgt+dtRec.Wgt+dtExcess.Wgt)-(dtSale.Wgt+dtIss.Wgt+dtShort.Wgt))<br/>      as ClsWgt
    	,C.ShortName
    
    
    from tblCounterDetails Cd 
     join tblCounter C on Cd.CounterId = C.Id
     join tblItem IT on IT.Id = Cd.ItemId 
     join --Purchase
    	(
    	select	Cd.StockId as Id 
    	,Cd.CounterId as Counter
    	,isnull(sum(Pd.Unit),0)as Unit
    	,isnull(sum(Pd.GWgt),0)as Wgt 
    from tblCounterDetails Cd
    	left join tblPurchaseDetails Pd on Pd.ItemId = Cd.StockId and Pd.CounterNo = Cd.CounterId 
    	group by Cd.StockId,Cd.CounterId
    	)
    	as dtP on dtP.Id = Cd.StockId and dtP.Counter = Cd.CounterId
    
     join --return
    	(
    select	Cd.StockId as Id
    	 ,Cd.CounterId as Counter
    	,isnull(sum(Rd.Unit),0)as Unit
    	,isnull(sum(Rd.GWgt),0)as Wgt from tblCounterDetails Cd 
    left join tblReturnDetails Rd on Rd.ItemId = Cd.StockId and Rd.CounterNo = Cd.CounterId 
    	group by Cd.StockId,Cd.CounterId
    	)
    	as dtRet on dtRet.Id = Cd.StockId and dtRet.Counter = Cd.CounterId
    left join --recipt
    	(
    SELECT Cd.CounterId AS Counter
    , Cd.StockId AS Id
    , ISNULL(SUM(Isd.Unit), 0) AS Unit
    , ISNULL(SUM(Isd.GWgt), 0) AS Wgt
    FROM tblCounterDetails as Cd 
    LEFT OUTER JOIN tblIssueDetails Isd ON Isd.CNo = Cd.CounterId 
    AND Isd.ItemId = Cd.StockId 
    left JOIN tblIssue ON tblIssue.Id = Isd.IId 
    where tblIssue.TaskType = 1
    GROUP BY Cd.StockId, Cd.CounterId
    	)
    	as dtRec on dtRec.Id = Cd.StockId and dtRec.Counter = Cd.CounterId
    join --Sales
    	(
    select	Cd.StockId as Id
    	,Cd.CounterId as Counter
    	,isnull(sum(Sd.Unit),0)as Unit
    	,isnull(sum(Sd.GWgt),0)as Wgt from tblCounterDetails Cd 
    left join tblSalesDetails Sd on Sd.ItemId = Cd.StockId and Sd.CounterNo = Cd.CounterId 
    	group by Cd.StockId,Cd.CounterId
    	)
    	as dtSale on dtSale.Id = Cd.StockId and dtSale.Counter = Cd.CounterId
     left join --Issue
    	(
    SELECT   
    Cd.CounterId AS Counter
    , Cd.StockId AS Id
    , ISNULL(SUM(Isd.Unit), 0) AS Unit
    , ISNULL(SUM(Isd.GWgt), 0) AS Wgt
    FROM tblCounterDetails as Cd 
    LEFT OUTER JOIN tblIssueDetails Isd ON Isd.CNo = Cd.CounterId 
    AND Isd.ItemId = Cd.StockId 
    left JOIN tblIssue ON tblIssue.Id = Isd.IId 
    where tblIssue.TaskType = 0
    GROUP BY Cd.StockId, Cd.CounterId
    	)
    	as dtIss on dtIss.Id = Cd.StockId and dtIss.Counter = Cd.CounterId
    join--short
    	(
    select	Cd.StockId as Id
    	,Cd.CounterId as Counter
    	,isnull(sum(Sh.Unit),0)as Unit
    	,isnull(sum(Sh.GWgt),0)as Wgt from tblCounterDetails Cd 
    left join tblShortExcess Sh on Sh.ItemId=Cd.ItemId and Sh.CounterNo=Cd.CounterId and Sh.TaskType = 0						       
    	group by Cd.StockId,Cd.CounterId
    	)
    	as dtShort on dtShort.Id=Cd.StockId and dtShort.Counter = Cd.CounterId
    
    join--Excess
    	(
    select	Cd.StockId as Id
    	,Cd.CounterId as Counter
    	,isnull(sum(Sh.Unit),0)as Unit
    	,isnull(sum(Sh.GWgt),0)as Wgt from tblCounterDetails Cd 
    left join tblShortExcess Sh on Sh.ItemId=Cd.ItemId and Sh.CounterNo=Cd.CounterId and Sh.TaskType = 1						       
    	group by Cd.StockId,Cd.CounterId
    	)
    	as dtExcess on dtExcess.Id=Cd.StockId and dtExcess.Counter = Cd.CounterId
    	order by Cd.CounterId 
    


    here in the --issue line and --receipt line,i have a problem that when i use simple join the query does not give any output..

    but when i use left join for both processes it returns correct value....but the sum will not  displayed...

    i think the sum operation cannot able to access the value.....

     

     

    please help me.......

    thanks in advance!!!!!!!!!!!!!!


    Seminar Topics For Computer Science 2010
    Thursday, June 16, 2011 8:31 AM
  • Thank you for your explanation.  I have read various sites and still didn't understand.  I read yours the first time and it "clicked".  I got it.  Thank you
    Tuesday, August 21, 2012 1:55 PM
  • To All:

    It is helpful to future readers if you are more specific when thanking.

    Also, don't forget to VOTE UP if you find a post helpful.

    Thanks.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Friday, September 28, 2012 11:09 PM