Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    13

    Exclamation Unanswered: problem in query

    Hi, i'm having problem in query. A table with 2 fields, name and salary. I create a query to display the sum of salary if the salary is larger than a certain amount. The sample code is like this :

    SELECT 'sum(salary) AS total'
    FROM tblUser
    WHERE 'total' > 4000;

    but it keep giving me "Data Type Mismatch" error message. Then i separate the query into 2, the first display the total and the second check the total. In this case, no problem. But this method is not suitable in my programming logic. So, anyone have any idea ??

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    don't use quotes

    SELECT sum(salary) AS total
    FROM tblUser
    WHERE salary > 4000
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Posts
    13

    Thumbs up

    Thanks. By the way, i also found a way to solve this problem...

    SELECT aaa
    FROM (SELECT sum(salary) AS aaa FROM tblUser)
    WHERE aaa > 4000;

    It seem your way is better. Thanks again.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, the solution i gave in #2 is totally different from yours in #3

    apples and oranges

    they produce different results

    do you have a test table for this? you'll only need a few rows to see...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2004
    Posts
    13
    I tested directly using MS Access, but your sql code was not stable. No error but sometime ok sometime no output after i added extra record rows on tblUser. I also tried the sql code on VB6, ASP and ASP.Net but the results remain the same. Possibly the different version of MS Access ? I'm using MS Access 2000.

    The sql code i wrote in #3 was fine, i got the correct output but dont know why MS Access will add ". AS [%$##@_Alias]" behind "FROM" of #3.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    access adds the alias because you're using a derived table

    my query and your query get different results

    my query gets the sum of salaries if the salary is larger than a certain amount, which is what you asked for

    your query gets the sum of salaries if the sum is larger than a certain amount, which is not what you asked for

    totally different queries

    what did you mean by stable? they just don't get more stable than the one i gave you

    good luck and have fun
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2004
    Posts
    13
    Oh, now i understand. Sorry if i'm confusing you, what i mean is to display the sum if the sum is bigger than a certain amount. About the unstable, when i change the amount of "salary" in "tblUser" then the output of the query will be empty. Maybe is like what you said, if the "salary" of first record is bigger than a certain amount then it will display the sum. Anyway, i still want to say thanks.

Posting Permissions

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