Professional Web Applications Themes

MySQL + ASP + SUM = problem - MySQL

Hi, I have a very odd problem I'm doing SELECT *,SUM(gamescount.ncount) AS ntotal FROM gamescount LEFT JOIN games ON games.cgame=gamescount.cgame where games.cgame='GAME-87' GROUP by gamescount.cgame I have three machines, my local test server, my old main website server and my new website server (just gone live and now failing!). This query works in all three servers directly in the MySQL console as expected. However, on the new server it returns no records when run via an ASP webpage (works correctly on other two servers). If I change the SUM to a COUNT,it works and returns 4 (i.e. there are 4 ...

  1. #1

    Default MySQL + ASP + SUM = problem

    Hi,

    I have a very odd problem I'm doing

    SELECT *,SUM(gamescount.ncount) AS ntotal FROM gamescount LEFT JOIN games ON
    games.cgame=gamescount.cgame where games.cgame='GAME-87' GROUP by
    gamescount.cgame

    I have three machines, my local test server, my old main website server and
    my new website server (just gone live and now failing!).

    This query works in all three servers directly in the MySQL console as
    expected. However, on the new server it returns no records when run via an
    ASP webpage (works correctly on other two servers).

    If I change the SUM to a COUNT,it works and returns 4 (i.e. there are 4
    matching records) but I want the SUM of them instead.

    The three machines are identical (he says!), so I can't see what might be
    different. Clearly MySQL is configured correctly, maybe something about the
    ODBC connection is different (it looks the same to me).

    No errors, just an empty recordset.

    Same version of ODBC drivers (3.51), same version of MDAC (2.82.1830.0).

    Any ideas?

    If anyone can suggest a more pertinent newsgroup, then please do so.

    --
    Kev



    Kevin Guest

  2. #2

    Default Re: MySQL + ASP + SUM = problem

    "Kevin Stone" <com> wrote: 

    Ridiculous query!

    1. You're doing a LEFT JOIN, collecting all rows from gamescount but
    in WHERE you throw away all the NULL rows created for rows missing
    in the games table. An INNER JOIN would be more appropriate.

    2. You group on the cgame column and at the same time have cgame=const
    in WHERE. So GROUP BY effectively collapses all rows. You will get
    the same without using GROUP BY at all.

    3. You SELECT * - this alone is an offense already. But further more,
    you select aggregated values and columns not in GROUP BY. The value
    of all result columns except ntotal and cgame is undefined, at least
    for the table(s) where cgame is not UNIQUE.

    I would suggest to rewrite as subquery:

    SELECT ... /* do not use *, name the columns you need */
    (SELECT SUM(ncount) FROM gamescount WHERE cgame=games.cgame) AS ntotal
    FROM games
    WHERE cgame='GAME-87'

    Note1: I assume cgame is UNIQUE across the games table.
    Note2: you need MySQL 4.1 or later for the subquery.
     

    If the data is same and the query is same, it must be a configuration
    or version mismatch.
     

    Then it is not a database problem.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  3. #3

    Default Re: MySQL + ASP + SUM = problem

    Hi,
     

    Not a database expert, sorry. I needed some data, did it the best way I
    thought.
     

    Point taken. It works as expected, however, I still have the same problem.
    Works in 5 of the 6 places, but not where needed (on the live ASP server).
    It's the SUM that does it, COUNT works a treat.
     
    > Then it is not a database problem.[/ref]

    Agreed, but then I knew this already, but as this newsgroup is frequented by
    experts someone may know what to do or where to look for the problem. Which
    is why I said:
     

    Thanks anyway.

    --
    Kev


    Kevin Guest

  4. #4

    Default Re: MySQL + ASP + SUM = problem

     [/ref]
     

    The example in the posting is for one record to demonstrate my problem, in
    the live version I'm after to top 10, so cgame=const won't exist.

    Again, SELECT * is for this posting, I always select the fields I need in
    the live system. I'm running a system which has a few hundred thousand
    database calls a day, so I'm always looking for ways to speed it up.

    --
    Kev


    Kevin Guest

  5. #5

    Default Re: MySQL + ASP + SUM = problem


    Kevin Stone wrote: [/ref]

    >
    > The example in the posting is for one record to demonstrate my problem, in
    > the live version I'm after to top 10, so cgame=const won't exist.
    >
    > Again, SELECT * is for this posting, I always select the fields I need in
    > the live system. I'm running a system which has a few hundred thousand
    > database calls a day, so I'm always looking for ways to speed it up.
    >
    > --
    > Kev[/ref]

    Hi Kevin,

    I'm having the same problem as use by the sounds of it. Select * works
    on all servers except for our live web server. Again, it works fine in
    MySQL but not through the web server (ASP). "Select * From VAT"
    returns a recordset with two records yet it is on EOF so I can't get at
    the rows, whereas "Select VATID From VAT" works a treat?!

    I hope you got an answer and if so please reply!

    Thanks in advance.

    jamie07051975@gmail.com Guest

  6. #6

    Default Re: MySQL + ASP + SUM = problem

    >> >> SELECT *,SUM(gamescount.ncount) AS ntotal FROM gamescount LEFT JOIN 
    >>[/ref][/ref]
     

    I've no resolution yet, I've no idea what the difference is! And it's so
    frustrating.

    --
    Kev


    Kevin Guest

  7. #7

    Default Re: MySQL + ASP + SUM = problem

    > I'm having the same problem as use by the sounds of it. Select * works 

    Fixed it!

    It was a problem with the ODBC Mysql 3.51.09 driver. Not spotted it was
    older than the 3.51.12 on the other machines. Upgraded and problem sorted.

    --
    Kev


    Kevin Guest

Similar Threads

  1. Replies: 0
    Last Post: January 29th, 02:03 PM
  2. mySQL CF5 TCP/IP Problem
    By WeeJames in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 24th, 09:57 AM
  3. Replies: 2
    Last Post: July 2nd, 09:21 PM
  4. Replies: 3
    Last Post: October 28th, 05:28 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139