aggregate function done wrongly[Answered] RSS

• • Reply
  arwa55

  Member

  22 Points

  112 Posts

  aggregate function done wrongly

  Oct 20, 2016 12:27 PM|arwa55|LINK

  hiii im trying to this code 

  SELECT       placeName ,picURL,  counterDate , MAX(counter) 
  FROM            Place
  WHERE convert(varchar(10), counterDate, 102) = convert(varchar(10), getdate(), 102)
  GROUP BY counterDate 
  

  it didnt work i want the row with the max counter column

  i keep getting 

   Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

  how this can be done in another way 

• • Reply
  oned_gk

  All-Star

  52813 Points

  15767 Posts

  Re: aggregate function done wrongly

  Oct 21, 2016 12:55 AM|oned_gk|LINK

  SELECT       placeName ,picURL,  counterDate , MAX(counter) 
  FROM            Place
  WHERE convert(varchar(10), counterDate, 102) = convert(varchar(10), getdate(), 102)
  GROUP BY counterDate,placeName ,picURL

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  arwa55

  Member

  22 Points

  112 Posts

  Re: aggregate function done wrongly

  Oct 21, 2016 06:43 AM|arwa55|LINK

  Hiii thanku for ur reply but that didn't work I tried it I get all rows cause they are grouped by the place name I need the result to be the row with the max counter
• • Reply
  Brando ZWZ

  Star

  9831 Points

  3120 Posts

  Re: aggregate function done wrongly

  Oct 22, 2016 12:24 PM|Brando ZWZ|LINK

  Hi arwa55,

  arwa55

  aggregate function done wrongly

  As far as I know, this error means sql couldn't find the placeName ,picURL columns which is selected by the group.

  If we use group by, we could only select the group by's column and aggregate functions (SUM, MIN, MAX, etc.).

  So you will get this error.

  I suggest you could write a database demo to us and write a result demo to us, so that it will be more easily for us to find the solution to achieve your requirement.

  For example:

  Database table:

  a   b
  --------
  1   abc
  1   def
  1   ghi
  2   jkl
  2   mno
  2   pqr

  The result I want to achieve：

  a   x
  --------
  1   ghi
  2   pqr

  Best Regards,

  Brando

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
  
• • Reply
  Nasser Malik

  Star

  13450 Points

  2764 Posts

  Re: aggregate function done wrongly

  Oct 22, 2016 04:23 PM|Nasser Malik|LINK

  Hi Arwa,

  You can do it like this way

  SELECT  placeName ,
          picURL ,
          counterDate ,
          counter
  FROM    Place
  WHERE   CONVERT(VARCHAR(10), counterDate, 102) = CONVERT(VARCHAR(10), GETDATE(), 102)
          AND counter = ( SELECT  MAX(counter)
                          FROM    Place
                          WHERE   CONVERT(VARCHAR(10), counterDate, 102) = CONVERT(VARCHAR(10), GETDATE(), 102)
                        );
  

  Please Mark as Answer if find helpful -- Nasser --
  Skype: maleknasser1
  LinkedIn: https://www.linkedin.com/in/maliknasser
  
• • Reply
  arwa55

  Member

  22 Points

  112 Posts

  Re: aggregate function done wrongly

  Oct 24, 2016 08:11 AM|arwa55|LINK

  it worked perfectly thanku