MySQL Forums
Forum List  »  Newbie

Re: subquery
Posted by: Felix Geerinckx
Date: October 19, 2005 05:21AM

J L wrote:

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

SELECT
domainTable.name,
sum(NetworkTraffic.download) as "Weekly",
sum(IF(date="2005-10-12", NetworkTraffic.download, 0)) 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

--
felix
Please use BBCode to format your messages in this forum.

Options: ReplyQuote


Subject
Written By
Posted
J L
October 19, 2005 04:20AM
Re: subquery
October 19, 2005 05:21AM
J L
October 19, 2005 05:29AM
J L
October 19, 2005 05:37AM
October 19, 2005 05:54AM
J L
October 19, 2005 06:08AM
October 19, 2005 07:20AM
J L
October 19, 2005 08:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.