...

View Full Version : count syntax



BubikolRamios
10-04-2011, 12:50 PM
select id_galery,count(id)=2 from galery_optional_inf
where id in (13)


This is legal, as it executes. Any explanation what "=2" does ?

Old Pedant
10-04-2011, 11:37 PM
It compares the value of COUNT(id) with 2 and produces either TRUE or FALSE.

Because MySQL doesn't support a *true* boolean type, TRUE is converted to the integer (or bit) value 1. FALSE becomes 0.



mysql> select * from zap;
+------+-------+
| id | flags |
+------+-------+
| 1 | 10 |
| 2 | 6 |
| 3 | 9 |
+------+-------+
3 rows in set (0.25 sec)

mysql> select count(id) = 2 from zap;
+---------------+
| count(id) = 2 |
+---------------+
| 0 | [in other words, "FALSE"]
+---------------+
1 row in set (0.05 sec)

mysql> select count(id) = 3 from zap;
+---------------+
| count(id) = 3 | [in other words, "TRUE"]
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

BubikolRamios
10-05-2011, 07:01 AM
Is there any practical/performance use of it ?
Besides that it works I cant see any.

Keleth
10-05-2011, 04:26 PM
Well, lets say you want to make a forum with a VIP club... eg, everyone over a certain number of posts. You can do COUNT(*) = 1000 or COUNT(*) > 1000, etc, and it'll show if someone is or isn't a VIP.

Old Pedant
10-05-2011, 07:14 PM
Good example, Keleth. It's nothing that the host language (e.g., PHP/ASP/JSP) couldn't do, of course, but sometimes it's nice to do it in the query, instead.

BubikolRamios
10-05-2011, 07:21 PM
Thanks to both.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum