Professional Web Applications Themes

Avoiding subquery - MySQL

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

  1. #1

    Default Avoiding subquery

    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é


    André Hänsel Guest

  2. #2

    Default Re: Avoiding subquery

    "André Hänsel" <andrewebkr.de> wrote in message
    news:dt3672$7ou$1sagnix.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.


    Bill Karwin Guest

  3. #3

    Default Re: Avoiding subquery

    Bill Karwin wrote:
    > "André Hänsel" <andrewebkr.de> wrote in message
    > news:dt3672$7ou$1sagnix.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.


    André Hänsel Guest

  4. #4

    Default Re: Avoiding subquery

    "André Hänsel" <andrewebkr.de> wrote in message
    news:dt3bg5$8qk$1sagnix.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.


    Bill Karwin Guest

Similar Threads

  1. Subquery
    By Ruszaj in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 2nd, 04:50 PM
  2. using list in subquery
    By spacehog in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: March 9th, 11:23 PM
  3. SQL subquery question
    By derek in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 06:48 PM
  4. Select From SubQuery
    By SriSamp in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 09:28 AM
  5. subquery with more than 1 fields
    By Calvin in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 05:53 AM

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