• SQL Query using the AVG function

    Author
    Topic
    #477968

    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

    Viewing 9 reply threads
    Author
    Replies
    • #1289492

      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…

    • #1289496

      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.

      • #1289502

        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.

      • #1289503
        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.

        • #1289504

          That did correct the FROM ( error but I still get a syntax error at the AS DonatingDerivedTable.

    • #1289505

      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.

      28477-Test

    • #1289513

      Thanks for your help! I’ll play with this a bit and see what I missed.
      Meleia

    • #1289517

      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.

    • #1289520

      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:

      28478-TestUpdated

      • #1289523

        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’

    • #1289524

      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?

    • #1289526

      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

    • #1289529

      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.

      • #1289530

        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!

    • #1289579

      Glad to be of help :).

    Viewing 9 reply threads
    Reply To: SQL Query using the AVG function

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: