none
You tried to execute a Query that does not include specific expression Date1 as part of aggregate function

    Question

  • hi i have a query 
    select UserName,Date1,NumberOfPages from details where Date1 between CDate('3/1/2010') And  CDate('3/29/2010') 
    And UserName ='6025' GROUP BY UserName HAVING(SUM(NumberOfPages) > 5)

    i have to get all the records which is in between the date specified for a pirticular user who has humber of pages mor than 5

    but it give the error

    "You tried to execute a Query that does not include specific expression Date1 as part of aggregate function"

    Can any one know abou this error

     

    Sunday, March 28, 2010 1:09 PM

Answers

  • If the column is not included into group by, then it should be included in any of the aggregate functions. So, this query

    select UserName,Date1,NumberOfPages from details where Date1 between CDate('3/1/2010') And  CDate('3/29/2010')
    And UserName ='6025' GROUP BY UserName HAVING(SUM(NumberOfPages) > 5)

    is incorrect and this query

    select UserName,Max(Date1) as LastDate,sum(NumberOfPages) as TotalPages from details where Date1 between '20100301' And  '20100329' And UserName ='6025' GROUP BY UserName HAVING(SUM(NumberOfPages) > 5)

    will work. Also, what is cDate function? Are you using SQL Server?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by roma_victa Monday, March 29, 2010 7:48 AM
    Sunday, March 28, 2010 1:40 PM
    Moderator

All replies

  • hi

    even this query gives the same error

    select UserName,Date1,NumberOfPages from details where Date1 = CDate('3/28/2010') And UserName ='6025' GROUP BY UserName HAVING(SUM(NumberOfPages) > 5)

    Sunday, March 28, 2010 1:23 PM
  • If the column is not included into group by, then it should be included in any of the aggregate functions. So, this query

    select UserName,Date1,NumberOfPages from details where Date1 between CDate('3/1/2010') And  CDate('3/29/2010')
    And UserName ='6025' GROUP BY UserName HAVING(SUM(NumberOfPages) > 5)

    is incorrect and this query

    select UserName,Max(Date1) as LastDate,sum(NumberOfPages) as TotalPages from details where Date1 between '20100301' And  '20100329' And UserName ='6025' GROUP BY UserName HAVING(SUM(NumberOfPages) > 5)

    will work. Also, what is cDate function? Are you using SQL Server?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by roma_victa Monday, March 29, 2010 7:48 AM
    Sunday, March 28, 2010 1:40 PM
    Moderator