count,sum

[Frank Beerens]

Hello

this query works

SELECT prettyname,
SUM( STATUS =5 ) AS STATUS5,
SUM( STATUS <>1 and STATUS<>5 ) AS STATUS2,
SUM( STATUS =1 ) AS STATUS1
FROM serversalive
GROUP BY prettyname

this one doesn't

SELECT prettyname,
COUNT( STATUS =5 ) AS STATUS5,
COUNT( STATUS <>1 and STATUS<>5 ) AS STATUS2,
COUNT( STATUS =1 ) AS STATUS1
FROM serversalive
GROUP BY prettyname

does anybode know why

rgds Frank

[Andy Hassall]

On Sat, 20 Mar 2004 18:05:16 +0100, "Frank Beerens" <FrankE...@zonnet.nl>
wrote:

>

Don't multipost. Answer crossposted to alt.comp.lang.php and alt.php.sql.

Depends what you mean by "doesn't work". They're both pretty dubious.

Since you haven't provided any information, I'm making the assumption that
you're running MySQL, which is the only database I know lets you use aggregates
of Boolean expressions like that at all.

mysql> create table t (status int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select sum(status=5), count(status=5) from t;
+---------------+-----------------+
| sum(status=5) | count(status=5) |
+---------------+-----------------+
| 1 | 5 |
+---------------+-----------------+
1 row in set (0.02 sec)

Both SUM and COUNT "work" - this is the expected result.

SUM adds up values. MySQL lets you use Boolean expressions as integers, with
false evaluating as 0 and true evaluating as 1. So you can SUM them together.

COUNT counts for how many rows the given expression is not null. Your Boolean
expressions are always not null, being either 0 or 1, so you get the same
result as COUNT(*).

A more standards-compliant way of writing the same would be something like:

SELECT prettyname,
SUM(CASE WHEN STATUS = 5 THEN 1 ELSE 0 END) AS STATUS5,
SUM(CASE WHEN STATUS <> 1 AND STATUS <> 5 THEN 1 ELSE 0 END) AS STATUS2,
SUM(CASE WHEN STATUS = 1 THEN 1 ELSE 0 END) AS STATUS1

FROM serversalive
GROUP BY prettyname

--
Andy Hassall <an...@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

[Frank Beerens]

"Andy Hassall" <an...@andyh.co.uk> schreef in bericht
news:ff0p5090at599gdas...@4ax.com...

Ok maybe it works like you did, but i did a group by and there is the
result of count the count of all records of that group

Frank

[Andy Hassall]

On Sat, 20 Mar 2004 19:15:59 +0100, "Frank Beerens" <FrankE...@zonnet.nl>
wrote:

>> mysql> select sum(status=5), count(status=5) from t;

Yes, you would do. That's what I said.

mysql> select * from serversalive;
+------------+--------+
| prettyname | status |
+------------+--------+
| group1 | 1 |
| group1 | 2 |
| group1 | 3 |
| group1 | 4 |
| group1 | 5 |
| group2 | 1 |
| group2 | 2 |
| group3 | 3 |
| group3 | 4 |
| group3 | 5 |
+------------+--------+
10 rows in set (0.01 sec)

mysql> SELECT prettyname,
-> SUM( STATUS =5 ) AS STATUS5,
-> SUM( STATUS <>1 and STATUS<>5 ) AS STATUS2,
-> SUM( STATUS =1 ) AS STATUS1
-> FROM serversalive
-> GROUP BY prettyname
-> ;
+------------+---------+---------+---------+
| prettyname | STATUS5 | STATUS2 | STATUS1 |
+------------+---------+---------+---------+
| group1 | 1 | 3 | 1 |
| group2 | 0 | 1 | 1 |
| group3 | 1 | 2 | 0 |
+------------+---------+---------+---------+
3 rows in set (0.03 sec)

mysql> SELECT prettyname,
-> SUM(CASE WHEN STATUS = 5 THEN 1 ELSE 0 END) AS STATUS5,
-> SUM(CASE WHEN STATUS <> 1 AND STATUS <> 5 THEN 1 ELSE 0 END) AS STATUS2,
-> SUM(CASE WHEN STATUS = 1 THEN 1 ELSE 0 END) AS STATUS1
-> FROM serversalive
-> GROUP BY prettyname;
+------------+---------+---------+---------+
| prettyname | STATUS5 | STATUS2 | STATUS1 |
+------------+---------+---------+---------+
| group1 | 1 | 3 | 1 |
| group2 | 0 | 1 | 1 |
| group3 | 1 | 2 | 0 |
+------------+---------+---------+---------+
3 rows in set (0.00 sec)

mysql> SELECT prettyname,
-> COUNT( STATUS =5 ) AS STATUS5,
-> COUNT( STATUS <>1 and STATUS<>5 ) AS STATUS2,
-> COUNT( STATUS =1 ) AS STATUS1
-> FROM serversalive
-> GROUP BY prettyname;
+------------+---------+---------+---------+
| prettyname | STATUS5 | STATUS2 | STATUS1 |
+------------+---------+---------+---------+
| group1 | 5 | 5 | 5 |
| group2 | 2 | 2 | 2 |
| group3 | 3 | 3 | 3 |
+------------+---------+---------+---------+
3 rows in set (0.00 sec)

Both of these queries "work" in the sense that they're valid, and the results
are correct.

Whether they do what you want is a different matter, but you've not actually
said what you want to do.