Codeigniter: multiple COUNT and SUM from one table

Question

I have MySQL table as follow:

id | ads | status | user_id |

What would be best way to get:

count of all rows where user_id=1 and status=1
count of all rows where user_id=1 and status=0
sum of all ads where user_id=0
sum of all ads where user_id=1

My question: Can I run only one query to get all 4 results? If so, would you please explain how.

Thank you in advance.

And how do you want to do it? why codeigniter, do you want to do it with Active Record? — Federico J.
– Federico J., Commented Mar 20, 2014 at 23:00
Actually it can be regular MySQL query; disregard CI. Also I can get results by running 2 queries: one for condition: status=1; another query when status=0 ... But wonder if all of that can be done by running one single query. — virrion
– virrion, Commented Mar 20, 2014 at 23:06
Well, I thought it better, maybe with the query below you can do it, :P — Federico J.
– Federico J., Commented Mar 20, 2014 at 23:07

salih0vicX · Accepted Answer · 2014-03-20 23:43:23Z

1

Try this:

select sum(if(status=1 and user_id=X, 1, 0)) as V1,
sum(if(status=1 and user_id=X, ads, 0)) as V2,
sum(if(status=0 and user_id=X, 1, 0)) as V3,
sum(if(status=0 and user_id=X, ads, 0)) as V4
FROM table)

answered Mar 20, 2014 at 23:43

salih0vicX

1,3831 gold badge8 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Federico J. Over a year ago

XD, I see, I had to put all with sum, :P

Collectives™ on Stack Overflow

Codeigniter: multiple COUNT and SUM from one table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related