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

Answered here: http://forums.mysql.com/read.php?10,...0095#msg-50095

--
felix