Re: Doing sum's if certain conditions are true

[Visolve DB TEAM]

Hello Ian barnes

I hope this query will resolve your problem if my understanding is correct
SELECT code1,
SUM(bytes) as sumofbytes,
MAX(bytes)  as maximum,
MIN(bytes) as minimum,
COUNT(bytes) as bytecount,
SUM(duration) as duration,
AVG(bytes) as averagebyte
FROM mytable where code1<>'none' and code2<>'denied' group by code1


Thanks
Visolve Db team

----- Original Message ----- 
From: "Ian Barnes" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, August 29, 2006 3:26 PM
Subject: Doing sum's if certain conditions are true


Hi,

I am trying to build a query that does a sum if a certain parameter is 
set.
For example:

I have a row with four fields: code1, code2, duration and bytes. My 
current
query looks something like this: SELECT code1 as code, sum(bytes) as bin,
max(bytes) as min, avg(bytes) as ain, count(bytes) as cin, sum(duration) 
as
dur from data group by code; which returns something like this:

+---------------------------+--------------+-----------+-------------+----------+-------------+
| code                      | bin          | min       | ain         |
cin      | dur         |
+---------------------------+--------------+-----------+-------------+----------+-------------+
| NONE                      | 103939170759 | 485089817 |   3739.1827 |
27797297 | 11681839027 |

Now, what i need todo is exclude certain info from the above NONE entry if
code2 is equal to something. So for example (in php terminology):

if(code == 'NONE') {
 if(code2 == 'DENIED') { continue; }
 else {
     bin += bytes;
     if(bytes > min) { min = bytes; }
     cin++;
     dur += dur;
 }
}

after that i could work out the average by dividing bin / cin for what in
the report is called ain. Is there any way of achieving this via the sql
query because the above is a hugely tedious way of doing it. I know mysql
has an if() statement, but I have no idea how to implement it using what i
want to achieve above.

Thanks in advance.
Ian



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]