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 Stone" <newsaccount@HotPOP.com> wrote:
>
> 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

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.

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

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

> This query works in all three servers directly in the MySQL console as
> expected.

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/

Hi,

> Ridiculous query!

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

> 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'

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.

>> This query works in all three servers
> Then it is not a database problem.

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:

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

Thanks anyway.

--
Kev

>> 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

> 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.

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 Stone wrote:
> >> 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
>
> > 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.
>
> 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

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.

>> >> 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'm having the same problem as use by the sounds of it.

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

--
Kev

> 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!

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