CODEWITHSUNDEEP
mysqlselectcountrows
sergserg
sergserg

Reputation: 22234

Can I fire two select statements in a single MySQL command and count resulting rows?

Here's my code as is, working, but very slow:

    $graded = R::getAll("SELECT posts.id, posts.discussion, rating.rating, rating.itemid
                             FROM uv_forum_posts posts
                             JOIN uv_rating rating ON ( posts.id = rating.itemid )
                             WHERE posts.discussion = :discussion_id
                             GROUP BY posts.userid",
                             array(':discussion_id' => $discussion['id']));

    $total = R::getAll("SELECT posts.userid
                        FROM uv_forum_posts posts
                        WHERE posts.discussion = :discussion_id
                        GROUP BY userid",
                        array(':discussion_id' => $discussion['id']));

    $percentages[] = count($graded) / count($total) * 100;

$graded represents all rows that have a rating.

$total represents all user participation, regardless of being graded or not.

I'm only interested in the numerical values of the resulting sets, can I combine these two MySQL calls into a single call that returns two numbers graded and total?

Upvotes: 0

Views: 131

Answers (2)

peterm
peterm

Reputation: 92785

If I correctly understand your requirements you can try

SELECT p.userid,
       COUNT(*) total,
       COUNT(r.itemid) graded
  FROM uv_forum_posts p LEFT JOIN 
       uv_rating r ON p.id = r.itemid
 WHERE p.discussion = :discussion_id
 GROUP BY p.userid

Output:

| USERID | TOTAL | GRADED |
---------------------------
|      1 |     8 |      2 |
|      2 |     4 |      4 |

SQLFiddle

UPDATE: If you just want grand total for all posts and users then 

SELECT COUNT(*) total,
       COUNT(r.itemid) graded
  FROM uv_forum_posts p LEFT JOIN 
       uv_rating r ON p.id = r.itemid
 WHERE p.discussion = 1

Output:

| TOTAL | GRADED |
------------------
|    12 |      6 |

SQLFiddle

Upvotes: 1

Ajo Koshy
Ajo Koshy

Reputation: 1225

You could simply use a query as in the format : 

Select
( SELECT posts.id, posts.discussion, rating.rating, rating.itemid
                             FROM uv_forum_posts posts
                             JOIN uv_rating rating ON ( posts.id = rating.itemid )
                             WHERE posts.discussion = :discussion_id
                             GROUP BY posts.userid,
                             array(':discussion_id' => $discussion['id']))) gradeCount,

(SELECT posts.userid
                        FROM uv_forum_posts posts
                        WHERE posts.discussion = :discussion_id
                        GROUP BY userid,
                        array(':discussion_id' => $discussion['id'])))totalCount

Upvotes: 0

Related Questions