thanks in advance for your time, what I need to achieve is generate multiple columns result from a single query, the thing is this:
I got a table were is storing the next:
id | user_id | revision 1 1 Approved 2 1 Rejected 3 1 Pending 4 1 Pending 5 1 Pending
What I need is to get the next result:
Total | User | Pending | Rejected | Approved 5 1 3 1 1
I have tried using Group by like this:
SELECT count(id) Total, user_id User, revision FROM table1 GROUP BY user_id, revision
But the output is way to far from what I am spectating to get:
Total | User | revision 3 1 Pending 1 1 Rejected 1 1 Approved
The idea is to get the proper object returned to avoid over cycling the server side code with unnecessary processes, so If the database can do the handling in one call this would be great.
I tried googling for hours and all I could get were a lot of results for 'single column from multiple rows' which is something I do not need, thanks for your help in advance.
The final result set with many users should look like this:
Total | User | Pending | Rejected | Approved 5 1 3 1 1 5 2 4 0 1 5 7 2 3 0
Updated: After more researching I found this: http://stackoverflow.com/questions/14172282/sql-query-needed-to-get-result-in-two-columns-grouped-by-type-and-percentage-in
This is almost what I need after some editing I still can't get it to send it in solid numbers instead percentages, I lack the knowledge for working group cases, I will keep trying if any of you can help it is greatly appreciated.
Solution Hey I managed to do this, it was easy, but my lack of knowledge was in the middle, I read the chapter Generating Summaries in MySQL Cookbook, this is where they explain that you can create count, sum and other methods inside your query to gain the proper values in those columns you define, so this is the query I ended up using:
SELECT user_id as userid, count(id) Total, COUNT(IF(revision = 'pending', 1, NULL)) as pending, COUNT(IF(revision = 'approved', 1, NULL)) as approved, COUNT(IF(revision = 'rejected', 1, NULL)) as rejected FROM table1 GROUP BY user_id
This will fill those values by count only if criteria is met.