Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Unanswered: subquery using sum()

    The query below joins two tables (networktraffic and domaintable) and sums up the total download traffic for each domain.. domainTable is just a lookup between domainId and the name of the domain...

    SELECT domainTable.name, sum(NetworkTraffic.download) as "Weekly",(select sum(NetworkTraffic.download) from NetworkTraffic where date="2005-10-12") as "Daily"
    FROM `NetworkTraffic`
    LEFT JOIN domainTable ON domainTable.domainId = NetworkTraffic.domainId
    WHERE date between "2005-10-10" AND "2005-10-16"
    GROUP BY domainTable.name

    here is the result of the query

    +------------------+--------+--------+
    | name | Weekly | Daily |
    +------------------+--------+--------+
    | Default FTP Site | 566 | 591 |
    | Default Web Site | 2 | 591 |
    | net_tot | 714| 591 |
    | _total | 78 | 591 |
    +------------------+--------+--------+

    The daily does not group by domain, it calculates the same result all the time.. how can i do this? Subqueries cannot have group by, having or such clauses.. Do i have to rewrite the statement??

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122

Posting Permissions

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