aggregate function done wrongly [Answered]RSS

• • Reply
  arwa55

  Member

  22 Points

  109 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

  52001 Points

  15415 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

  109 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

  8331 Points

  2514 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

• • Reply
  Nasser Malik

  Star

  13440 Points

  2760 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

  109 Posts

  Re: aggregate function done wrongly

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

  it worked perfectly thanku