Problem while counting two fields in one query

Question

guys i need to count new private messages and old ones from the table

So the first thing that comes to mind is to use mysql_num_rows and it's easy to do

// check new pms $user_id = $userinfo['user_id']; $sql = "SELECT author_id FROM bb3privmsgs_to WHERE user_id='$user_id' AND (pm_new='1' OR pm_unread='1')"; $result = $db->sql_query($sql) ; $new_pms = $db->sql_numrows($result); $db->sql_freeresult($result); // check old pms $sql = "SELECT author_id FROM bb3privmsgs_to WHERE user_id='$user_id' AND (pm_new='0' OR pm_unread='0')"; $result = $db->sql_query($sql) ; $old_pms = $db->sql_numrows($result); $db->sql_freeresult($result); 

but how can I count these two fields in only one expression and shorter lines? ~

Answer 1

Use this query instead:

 SELECT SUM(CASE WHEN pm_new = '1' OR pm_unread = '1' THEN 1 ELSE 0 END) AS new_pms, SUM(CASE WHEN pm_new = '0' OR pm_unread = '0' THEN 1 ELSE 0 END) AS old_pms FROM bb3privmsgs_to WHERE user_id='$user_id' 

Here's a specific MySQL version that reads more cleanly:

 SELECT COUNT(IF(pm_new = '1' OR pm_unread = '1', 1, NULL)) AS new_pms, COUNT(IF(pm_new = '0' OR pm_unread = '0', 1, NULL)) AS old_pms FROM bb3privmsgs_to WHERE user_id='$user_id' 

Answer 2

MySQL will compare comparisons with 1 or 0. You can use SUM() to add the part of the WHERE that you tried to calculate the results.

This (MySQL dependent) is a shorter alternative to CASE WHEN examples.

 SELECT SUM(pm_new='1' OR pm_unread='1') as new_pms, SUM(pm_new='0' OR pm_unread='0') as old_pms FROM bb3privmsgs_to WHERE user_id='$userid' 

Answer 3

In SQL Server, you can do something like this:

 SELECT SUM(CASE WHEN pm_new='1' OR pm_unread='1' THEN 1 ELSE 0 END), SUM(CASE WHEN pm_new='0' OR pm_unread='0' THEN 1 ELSE 0 END) FROM bb3privmsgs_to WHERE user_id='$user_id' 

I suppose you can do the same in mySql, let me get back to you in detail ...

Answer 4

As a lazy alternative to some other suggestions:

 SELECT SUM(newPMS) AS newPMS, SUM(oldPMS) AS oldPMS FROM ( SELECT COUNT(author_id) AS newPMS, 0 AS oldPMS FROM bb3privmsgs_to WHERE user_id='$user_id' AND (pm_new='1' OR pm_unread='1') UNION SELECT 0 AS newPMS COUNT(author_id) AS oldPMS FROM bb3privmsgs_to WHERE user_id='$user_id' AND (pm_new='0' OR pm_unread='0') )