Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Mar 2010
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Exclamation SQL sever 2008, select multiple field and aggregate function

    I have 2 tables DIRproject and DIRrequest.

    In DIRproject i have: projectname, dateAdded, addedBy
    In DIRrequest i have: projectname, NumOfDrawing

    what i want is to populate a table of info with projectname, dateAdded, addedBy, NumOfRequest and totalqty.

    below is my sql, i got an error that say "I am trying to include a variable that is not included in aggregate function".
    how to solve this anyone!

    Code:
    SELECT DIRproject.projectname, DIRproject.dateAdded, DIRproject.addedBy, count(DIRrequest.projectname) as NumOfRequest, sum(DIRrequest.NumOfDrawing) as totalqty
    FROM DIRproject INNER JOIN DIRrequest
             ON(DIRproject.projectname=DIRrequest.projectname)
    WHERE #now()# - DateRequested <= #pastDay#
    GROUP BY DIRproject.projectname

  • #2
    New Coder
    Join Date
    Mar 2010
    Location
    nyc
    Posts
    31
    Thanks
    0
    Thanked 1 Time in 1 Post
    When working with aggregates you have to include columns that are not part of the aggregate in group by. In your case your "group by" should look like this

    group by DIRproject.projectname, DIRproject.dateAdded, DIRproject.addedBy

    Now, this will run and it may not give you records that you're after, but it will give you a starting point.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •