Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I just did it, but I can't answer my own question because my low leve of poster :) –  Joe Walker Jul 1 '13 at 18:25
add comment

2 Answers 2

up vote 1 down vote accepted

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, this is known as statistical queries and then 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.

share|improve this answer
add comment

You can try this :

SELECT  COUNT(ID) TOTAL ,
        USER_ID ,
        PENDING = ( SELECT  COUNT(REVISION) APPROVED
                    FROM    MYTABLE
                    WHERE   REVISION = 'pending'
                  ) ,
        REJECT = ( SELECT   COUNT(REVISION) APPROVED
                   FROM     MYTABLE
                   WHERE    REVISION = 'REJECT'
                 ) ,
        APPROVED = ( SELECT COUNT(REVISION) APPROVED
                     FROM   MYTABLE
                     WHERE  REVISION = 'APPROVED'
                   )
FROM    MYTABLE
GROUP BY USER_ID

share|improve this answer
    
Try to rewrite without CTEs. MySQL has not this feature. –  ypercube Jul 2 '13 at 6:52
    
YOU CAN TRY THIS NOW : –  user2404431 Jul 2 '13 at 7:28
    
actually I already solved it yesterday, using statistical queries works perfectly to match and not overload the query iterations. Thanks for your idea. –  Joe Walker Jul 2 '13 at 17:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.