CODEWITHSUNDEEP
phpmysql
Angpaur
Angpaur

Reputation: 1

Query to display grouped info from same table in more than one column and row

I'm struggling to make a single mysql query to get data from a table which holds information about results of test requests containing test cases. Each test case can have 4 different results. Structure looks like below (with example data):

testrequestsresultsID | testrequestsID | testcaseresultsID
1                     | 1              | 4
2                     | 1              | 2
3                     | 1              | 1
4                     | 1              | 1
5                     | 2              | 4
6                     | 2              | 4
7                     | 2              | 2
8                     | 2              | 2

My basic goal is to display in 4 different columns info about progress of test requests.

  1. First column should show total count of test requests,
  2. Second column should show finished test request ( testcaseresultsID is different than 4)
  3. Third column should show number of passed testcases ( testcaseresultsID equals 1)
  4. Forth column should show number of failed testcases ( testcaseresultsID equals 2)

I managed to get that info per a one test reques ID with below query:

SELECT 
(SELECT COUNT(*) AS `count` FROM `testrequestsresults` WHERE `testrequestsID`=1) `total`,
(SELECT COUNT(*) AS `count` FROM `testrequestsresults` WHERE `testrequestsID`=1 AND `testcaseresultsID`<>4) `finished`,
(SELECT COUNT(*) AS `count` FROM `testrequestsresults` WHERE `testrequestsID`=1 AND `testcaseresultsID`=1) `pass`,
(SELECT COUNT(*) AS `count` FROM `testrequestsresults` WHERE `testrequestsID`=1 AND `testcaseresultsID`=2) `fail`

This shows info for one request only.

My question is how to show extended info for many rows and with a front column showing testrequestsID? It should be displayed like below, considering the example data:

testrequestsID | total | finished | pass | fail
1              | 4     | 3        | 2    | 1
2              | 4     | 2        | 0    | 2

Thank you in advance for helping.

Upvotes: 0

Views: 50

Answers (2)

Strawberry
Strawberry

Reputation: 33945

SELECT testrequestsid
     , COUNT(*) total
     , SUM(testcaseresultsid <> 4) finished
     , SUM(testcaseresultsid = 1) pass
     , SUM(testcaseresultsid = 2) fail 
  FROM my_table 
 GROUP 
    BY testrequestsid;

Upvotes: 0

Zeljka
Zeljka

Reputation: 376

SELECT testrequestsID,
SUM IF(`testrequestsID`=1, 1,0) as total,
SUM IF(`testrequestsID`=1 AND `testcaseresultsID`<>4, 1, 0) AS finished,
SUM IF(`testrequestsID`=1 AND `testcaseresultsID`=1, 1, 0) AS pass,
SUM IF(`testrequestsID`=1 AND `testcaseresultsID`=2, 1, 0) AS fail
FROM
GROUP BY testrequestsID

Upvotes: 1

Related Questions