Avoiding subquery

[André Hänsel]

Hi,

if anyone feels like a little quiz (but I don't know the answer), just for
interest:

Is it possible to rewrite this:

SELECT domain,ftptraffic,(SELECT SUM(traffic) FROM monthlytraffic WHERE year
= '2005' AND monthlytraffic.domain = totaltraffic.domain) AS correction FROM
totaltraffic

or even this:

UPDATE totaltraffic SET ftptraffic = ftptraffic - (SELECT SUM(traffic) FROM
monthlytraffic WHERE year = '2005' AND monthlytraffic.domain =
totaltraffic.domain)

without using subqueries?

Since I use MySQL > 4.1 it works fine this way... I'm amazed. ;)

Regards,
André

[Bill Karwin]

"André Hänsel" <andre@webkr.de> wrote in message
news:dt3672$7ou$1@sagnix.uni-muenster.de...

> Is it possible to rewrite this:
>
> SELECT domain,ftptraffic,(SELECT SUM(traffic) FROM monthlytraffic WHERE
> year
> = '2005' AND monthlytraffic.domain = totaltraffic.domain) AS correction
> FROM
> totaltraffic

Ok, I'll give it a shot. Note that I haven't tested this, so caveat emptor.
:-)

SELECT t.domain, t.ftptraffic, SUM(m.traffic) AS correction
FROM totaltraffic AS t LEFT OUTER JOIN monthlytraffic AS m
ON m.domain = t.domain AND m.year = '2005'

By using the outer join, this delivers all the rows in totaltraffic, even if
there was no traffic for a given domain during 2005. So it should give the
same results as your query.

> or even this:
>
> UPDATE totaltraffic SET ftptraffic = ftptraffic - (SELECT SUM(traffic)
> FROM
> monthlytraffic WHERE year = '2005' AND monthlytraffic.domain =
> totaltraffic.domain)

UPDATE totaltraffic AS t, monthlytraffic AS m
SET t.ftptraffic = t.ftptraffic - m.traffic
WHERE m.domain = t.domain AND m.year = '2005'

You don't have to do the SUM this way. It does the subtraction for each
domain/month combination. But the end result is the same. This uses the
MySQL extended syntax for multi-table update, which is not standard SQL.

Regards,
Bill K.

[André Hänsel]

Bill Karwin wrote:

> "André Hänsel" <andre@webkr.de> wrote in message
> news:dt3672$7ou$1@sagnix.uni-muenster.de...

>> Is it possible to rewrite this:
>>
>> SELECT domain,ftptraffic,(SELECT SUM(traffic) FROM monthlytraffic
>> WHERE year
>> = '2005' AND monthlytraffic.domain = totaltraffic.domain) AS
>> correction FROM
>> totaltraffic

>
> Ok, I'll give it a shot. Note that I haven't tested this, so caveat
> emptor. :-)
>
> SELECT t.domain, t.ftptraffic, SUM(m.traffic) AS correction
> FROM totaltraffic AS t LEFT OUTER JOIN monthlytraffic AS m
> ON m.domain = t.domain AND m.year = '2005'
>
> By using the outer join, this delivers all the rows in totaltraffic,
> even if there was no traffic for a given domain during 2005. So it
> should give the same results as your query.

I didn't yet try to understand your variant, but it gives me the "Mixing of
group comlumns with non group columns is illegal withour GROUP BY"-error.

[Bill Karwin]

"André Hänsel" <andre@webkr.de> wrote in message
news:dt3bg5$8qk$1@sagnix.uni-muenster.de...

>> SELECT t.domain, t.ftptraffic, SUM(m.traffic) AS correction
>> FROM totaltraffic AS t LEFT OUTER JOIN monthlytraffic AS m
>> ON m.domain = t.domain AND m.year = '2005'
>>

> I didn't yet try to understand your variant, but it gives me the "Mixing
> of
> group comlumns with non group columns is illegal withour GROUP BY"-error.

Woops!

SELECT t.domain, t.ftptraffic, SUM(m.traffic) AS correction
FROM totaltraffic AS t LEFT OUTER JOIN monthlytraffic AS m
ON m.domain = t.domain AND m.year = '2005'
GROUP BY t.domain

I tried this (though with no data) and at least it doesn't give the error
about mixing of group columns.

Regards,
Bill K.