locked
aggregate function done wrongly RRS feed

Answers

  • User-183374066 posted

    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) );

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 22, 2016 4:23 PM

All replies

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

    Friday, October 21, 2016 12:55 AM
  • User-62667992 posted
    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
    Friday, October 21, 2016 6:43 AM
  • User283571144 posted

    Hi 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

    Saturday, October 22, 2016 12:24 PM
  • User-183374066 posted

    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) );

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 22, 2016 4:23 PM
  • User-62667992 posted

    it worked perfectly thanku

    Monday, October 24, 2016 8:11 AM