php - Combine two mysql COUNT(*) results, different columns

359

I'm trying to combine the counts for the following queries:

  SELECT recipient AS otheruser, COUNT(*) 
    FROM privatemessages 
   WHERE sender = '$userid' 
GROUP BY recipient

  SELECT sender AS otheruser, COUNT(*) 
    FROM privatemessages 
   WHERE recipient = '$userid' 
GROUP BY sender

If there anyway to combine these two queries, and add the count(*) together for each 'otheruser'

356

Answer

Solution:

The most straight forward way is to use SUM / Condition

SELECT
     CASE WHEN sender = '$userid' THEN recipient else sender END AS OtherUser,
     SUM(sender = '$userid') AS RCount,
     SUM(recipient = '$userid') AS SCount,
     COUNT(*) total
FROM
     privatemessages 
WHERE
     '$userid' IN (sender , recipient )
GROUP BY
     OtherUser

DEMO

Update

I cleaned up the SUM and GROUP BY statements thanks to deltab and Michael Buen

369

Answer

Solution:

The most straightforward way is just to use 2 queries.
There is absolutely no harm in requesting 2 different sets of data using 2 queries yet not a single reason to do it with one.

995

Answer

Solution:

You can combine them with UNION like this:

SELECT otheruser, COUNT(*) FROM
(SELECT recipient AS otheruser
FROM privatemessages 
WHERE sender = '$userid' 
GROUP BY recipient
UNION
SELECT sender AS otheruser
FROM privatemessages 
WHERE recipient = '$userid' 
GROUP BY sender)
374

Answer

Solution:

SELECT recipient AS r, sender AS s, COUNT(total) // can't use same alias
FROM privatemessages
WHERE sender = '$userid' AND recipient = '$userid'
GROUP BY s;

Then in your results function, call the count

while ($row = $result) {
  echo $row['COUNT(total)'];
}

I believe this query should work as expected. The only issue with your original was the dual alias and your grouping of two different fields. This is possible in 2 seperate queries but combined, it can't be done (at least the alias, don't think so with GROUP BY).

Hope this works for you.

UPDATE

A great alternative toCOUNT is to simply usenum_rows... Therefore, the query above would be:

SELECT recipient AS r, sender AS s // can't use same alias
FROM privatemessages
WHERE s = '$userid' AND r = '$userid'
GROUP BY s;

And then in your results function, callnum_rows function:

mysql_num_rows($result);

People are also looking for solutions to the problem: php - Wordpress authentication through a C++ program

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.