Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post

    SQL Query using the AVG function

    I am trying to find those Donors who donated more than an average number of times. I would have thought that the Count function would pass an integer to the AVG function but Iím missing something as I get this error:

    Msg 8117, Level 16, State 1,Line 1
    Operand data type varchar is invalid for avg operator.



    SELECT AVG('TotalTimesDonating') AS 'AverageTimesDonating'
    ,[Donors] AS 'DonorNames'
    FROM DonorsTable

    WHERE EXISTS
    (
    SELECT [Donors] AS 'DonorNames'
    ,COUNT([Donors])AS 'TotalTimesDonating'
    FROM DonorsTable
    GROUP BY 'DonorNames'
    )
    GROUP BY [DonorNames]
    HAVING 'TotalTimesDonating'>'AverageTimesDonating'

    I am completely new to SQL. Any and all suggestions appreciated!
    Meleia

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    One first issue here is the mixing of literal strings with field names and I am sure that is causing the error message.
    Another issue is the query itself. You are creating a query (the one that counts the totaltimes donating value) and then want to use it in a query to calculate the average of that value and then you want to use values from both these two queries. It simply is not possible to do what you are doing.

    My suggestion would be to calculate the average and store it somewhere or use it in a function, just to make the SQL much simpler to write. I may try to write something more complete later, but a calculation of the average could be something like this:

    Code:
    SELECT AVG(TotalTimesDonating) As AverageTimesDonating 
    FROM (
               SELECT Donors as Donornames, COUNT(Donors) as TotaltimesDonating
               FROM DonorsTable
               GROUP BY Donors
             )  As DonatingDerivedTable
    This query gives you the average you were looking for. Probably you could create a VBA function to return this average (conceptually easier) or you could try using the whole query in the WHERE clause of another query, just to give you the threshold value to select members who donated above this average value. If you go with the latter, be careful with the table names used...

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Ok, just going the easy way, this probably should work:

    Code:
    SELECT Donors as Donornames, COUNT(Donors) As TotalTimesDonating
    FROM DonorsTable
    GROUP BY Donors
    HAVING COUNT(Donors) > ( SELECT AVG(TotalTimes 
                                              FROM (
                                                         SELECT Donors as Donornames, COUNT(Donors) as Totaltimes
                                                         FROM DonorsTable
                                                         GROUP BY Donors
                                                        )  As DonatingDerivedTable 
                                             )
    I am not sure that it will work directly, but if not it should be pretty close.

    Sorry about the formatting. Can't get it aligned as I would like.
    Last edited by ruirib; 2011-07-23 at 07:57.

  4. #4
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Attempted your suggestion but am receiving syntax errors at FROM ( and at the derived table name.
    I'm totally new at this so everything is a learning lesson! Thanks.

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Code:
    SELECT Donors as Donornames, COUNT(Donors) As TotalTimesDonating
    FROM Donors
    GROUP BY Donors
    HAVING COUNT(Donors) > ( SELECT AVG(TotalTimes) 
                                              FROM (
                                                         SELECT Donors as Donornames, COUNT(Donors) as Totaltimes
                                                         FROM Donors
                                                         GROUP BY Donors
                                                        )  As DonatingDerivedTable 
                                             )
    Sorry there was a parenthesis missing after the AVG in the subquery. The code got all unformatted on my first attempt and I must have deleted it while trying to fix it. I was also using DonorsTable instead of just Donors as the table name. All that is now fixed.
    Last edited by ruirib; 2011-07-23 at 10:27.

  6. #6
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    That did correct the FROM ( error but I still get a syntax error at the AS DonatingDerivedTable.

  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I don't have your tables, but I copied this into access, just after selecting it and, of course, after creating a simple donors table with just the name of the donors as a column, and it worked for me.

    I am attaching a sample database. The query that matters to you is named OverallQuery. You will see that it has a lot more parenthesis than mine, but Access does add it's own unnecessary parenthesis.

    Test.zip

  8. #8
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Thanks for your help! I'll play with this a bit and see what I missed.
    Meleia

  9. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Let me know if you need further help. If the query does not work for you for some reason, posting the donors table structure surely will help me understand why.

    The test database also includes another query, named average, which is the subquery used to calculate the average. You can try and execute it on its own, and report any issues you may find.
    Last edited by ruirib; 2011-07-23 at 12:09.

  10. #10
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Checking this again I noticed that your table is named DonorsTable, as I initially had used it. I have updated the database and the queries, to reflect that:

    TestUpdated.zip

  11. #11
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    I copied your Test.mdb into SQL, applied it to my query andit worked perfectly! Guess my eyes and fingers were just not working together. As you said in your original post -- watch out for the names! Now I have one more piece, please. I need to know where to add this filteringpiece in to the query.
    HAVING [Donor] like '%.CA' or like '%.PA' and [Status] ='Active'

  12. #12
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Better tell me what that should mean. You want to obtain donors with a [donor] value ending in PA or CA and a [Status] value of active and having donated above average? The average, however, is to be calculated from all the donors?

  13. The Following User Says Thank You to ruirib For This Useful Post:

    Myers515 (2011-07-23)

  14. #13
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Sorry, those are other fields. I just went back to your database, modified the table and query then looked at the SQL and was able to add the line into the query where it belongs. Everything runs perfectly. Thanks so much for your help.
    Meleia

  15. #14
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Great. You are welcome .

    Anyway, as you are learning, let me stress the difference between a WHERE and a HAVING clause.
    With an aggregation query (one that calculates values over a set of records), the WHERE clause is "applied" first and it is used to determine which records will be used in the calculation of the values you want. A HAVING clause is applied once the values are calculated and the conditions in there should target calculated values.

    In a non informed look, I would say that these last conditions you added should be part of a WHERE clause and not of a HAVING clause. Most likely, in this case, the results will be the same, but that may not always be the case. Also, by restricting the records over which the calculations are performed, through the WHERE clause, you may speed up the completion of your query. These are two reasons to make sure that each condition goes into the proper clause.

    Of course, what you want to accomplish with each query needs to be taken into account when deciding this. Calculating values over all the records or only over a subset (depending on the WHERE clause) may as well give different calculated results.

  16. #15
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    When I ran the Access query I did end up using WHERE. Your explanation helps to clarify everything. My initial attempt seemed so logical, but I can see now why it didn't work. Again, thanks for everything. I've learned alot on this one! The Lounge is the BEST!

Page 1 of 2 12 LastLast

Posting Permissions

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