running this query
SELECT IF ( seen_flag+recent_flag = 0, 0, IF ( seen_flag = 1, 1, IF (
recent_flag = 1, 2, 0) ) ) AS flag, COUNT(*) FROM dbmail_messages WHERE
mailbox_idnr=6 AND (status < 2) group by seen_flag, recent_flag;
I got the following output:
+------+----------+
| flag | COUNT(*) |
+------+----------+
| 0 | 19 |
| 1 | 637 |
+------+----------+
2 rows in set (0.02 sec)
You see the flag 2 (recent_flag) is not returned.
using the current query:
SELECT 0,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=6 AND (status <
2) UNION SELECT 1,COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=6 AND
(status < 2) AND seen_flag=1 UNION SELECT 2,COUNT(*) FROM dbmail_messages
WHERE mailbox_idnr=6 AND (status < 2) AND recent_flag=1;
I got
+---+----------+
| 0 | COUNT(*) |
+---+----------+
| 0 | 657 |
| 1 | 637 |
| 2 | 0 |
+---+----------+
3 rows in set (0.42 sec)
You can see there is a 3rd row with count 0
If there is a mail the first result would look like this:
+------+----------+
| flag | COUNT(*) |
+------+----------+
| 0 | 19 |
| 2 | 1 |
| 1 | 637 |
+------+----------+
3 rows in set (0.04 sec)
Changing this would really reduce load on the database server.
Here is the version with CASE instead of IF, its more readable
SELECT
CASE
WHEN seen_flag+recent_flag = 0 THEN
0
WHEN seen_flag = 1 THEN
1
WHEN recent_flag = 1 THEN
2
END
AS flag, COUNT(*) FROM dbmail_messages WHERE mailbox_idnr=6 AND (status <
2) group by seen_flag, recent_flag;
hope you can test it.
Am 10.03.2013, 00:30 Uhr, schrieb Paul J Stevens <[email protected]>:
On 03/07/2013 10:27 AM, Harald Leithner wrote:
oh and
Does that provide the same information as the UNION
Yes it provides the same information but it could happend that it
doesnot return 3 rows, for example if you have not revent_flag messages
it doesn't return the count 0 so you have to preset the variable for 0
vaules.
I still don't understand. Can you provide some query output as an
example?
--
Harald Leithner
ITronic
Vogelweidplatz 12, 1150 Wien, Austria
Tel: +43-1-786 23 88
Fax: +43-1-98 52 077
Mobil: +43-699-123 78 4 78
Mail: [email protected] | itronic.at
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
