SQL: Joining 3 tables to generate report dashboard

All we need is an easy explanation of the problem, so here it is.

I am trying to join 3 different tables that holds my test execution results as "PASS", "FAIL" and "SKIP". There are 2 common properties in these 3 tables on the basis of which I need to club my result i.e. "BUILD_NUMBER" and "COMPONENT".

Tried several approach but does not get the desired result.

SQL: Joining 3 tables to generate report dashboard

Sample query:

select test_execution.COMPONENT, test_execution.BUILD_NUMBER, 
count(test_execution.TEST_STATUS) as PASS from (test_execution 
INNER JOIN test_execution_fail ON
test_execution.BUILD_NUMBER = test_execution_fail.BUILD_NUMBER) group by 
COMPONENT,BUILD_NUMBER;

My tables look like below:

CREATE TABLE test_execution_skip (
BUILD_NUMBER int,
TEST_NAME varchar(255),
TEST_CLASS varchar(255),
COMPONENT varchar(255),
TEST_STATUS varchar(255)
);

Other two tables are exactly same with test_execution and test_execution_fail as their names.

test_execution table holds 3 records(all pass values), test_execution_fail table holds 2 records (all fail values) and test_execution_skip table holds 1 record(skip value).

I want to populate data that will show me BUILD_NUMBER, COMPONENT, TOTAL, PASS, FAIL, SKIP as records where TOTAL, PASS, FAIL and SKIP will show the respectives counts.

Any help is appreciated here.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Here we see another instance of the "Table-per-Thing" Data Model and, once again, we see how it causes more problems than it solves.

Use one table – test_execution – and add a result column to it.
With proper indexing, you should have no performance issues.

Based on this one table, your query becomes [almost] trivial:

select 
  build_number
, component 
, sum( case when result = 'PASS' then 1 else 0 end ) passes 
, sum( case when result = 'FAIL' then 1 else 0 end ) fails 
, sum( case when result = 'SKIP' then 1 else 0 end ) skips 
from text_execution 
group by 
  build_number
, component 
order by 
  build_number
, component 
;

(Edit: Explanatory notes, as requested)

Given this data to start with:

select * 
from test_execution ; 

| component       | build_number | result | 
| Edge_Management |            1 | PASS   |
| Edge_Management |            1 | FAIL   |
| Edge_Management |            2 | PASS   |
| Edge_Management |            2 | SKIP   |
| Edge_Management |            2 | PASS   |
| Edge_Management |            3 | FAIL   |

The case clauses are the SQL equivalent of programming’s IF..THENs.
They calculate values on the fly, without storing them anywhere, here converting the textual result field into individual elements that we can add up:

select 
  build_number
, component 
, result 
, sum( case when result = 'PASS' then 1 else 0 end ) passes 
, sum( case when result = 'FAIL' then 1 else 0 end ) fails 
, sum( case when result = 'SKIP' then 1 else 0 end ) skips 
from text_execution ; 

| component       | build_number | result | passes | fails | skips | 
| Edge_Management |            1 | PASS   |      1 |     0 |     0 |
| Edge_Management |            1 | FAIL   |      0 |     1 |     0 |
| Edge_Management |            2 | PASS   |      1 |     0 |     0 |
| Edge_Management |            2 | SKIP   |      0 |     0 |     1 |
| Edge_Management |            2 | PASS   |      2 |     0 |     0 |
| Edge_Management |            3 | FAIL   |      0 |     1 |     0 |

Then, the SUM() function and "GROUP BY" clauses work together to produce totals of each different result, as above.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply