samuel_lopez Posted November 25, 2015 Share Posted November 25, 2015 (edited) Hi I have a query that look like this SELECT Count(tbltesttransactions.Trans_ID) as Passed ,tbltesttransactions.Status_ID as status ,tbltesttransactions.projectid as project FROM tbltesttransactions WHERE tbltesttransactions.Status_ID = '1' UNION SELECT Count(tbltesttransactions.Trans_ID) as Failed ,tbltesttransactions.Status_ID as status ,tbltesttransactions.projectid as project FROM tbltesttransactions WHERE tbltesttransactions.Status_ID = '2' But its output is like below:counter status project 5 Passed project1 2 Failed project1 1 Passed project2 3 Failed project2I want my output like this:Project Passed Failed project1 5 2 project2 1 3Please help me Your help is much appreciated. Edited November 25, 2015 by samuel_lopez Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted November 25, 2015 Share Posted November 25, 2015 What is your current code for receiving/outputting the results of your query? Quote Link to comment Share on other sites More sharing options...
samuel_lopez Posted November 25, 2015 Author Share Posted November 25, 2015 Hi Ch0cu3r. THis is my code to output. I used Php while ($row = $res->fetch_assoc()): ?> <tr> <td><?php echo strtoupper($row['project']); ?></td> <td><?php echo strtoupper($row['status']); ?></td> <td><?php echo strtoupper($row['counter']); ?></td> </tr> <?php endwhile; ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted November 25, 2015 Share Posted November 25, 2015 try SELECT projectid as project , SUM(IF(Status_ID=1, 1,0)) as passed , SUM(IF(Status_ID=2, 1,0)) as failed FROM tbltesttransactions GROUP BY projectid Quote Link to comment Share on other sites More sharing options...
Solution Psycho Posted November 25, 2015 Solution Share Posted November 25, 2015 @SamuelLopez: Are there more than just the two statuses? If not, you should change the field to a Boolean. E.g. name the field "passed" and use 1 (TRUE) to indicate passed and 0 (FALSE) to indicate not passed. @Barand, Correct me if I am wrong, but I don't think the IF() statements are needed - just the conditions. A condition will resolve to either TRUE (1) or FALSE (0). So, this should work as well: SELECT projectid as project , SUM(Status_ID=1) as passed , SUM(Status_ID=2) as failed FROM tbltesttransactions GROUP BY projectid 1 Quote Link to comment Share on other sites More sharing options...
samuel_lopez Posted November 27, 2015 Author Share Posted November 27, 2015 Hi Psycho. THank you. Your code works like a charm Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.