Using formulas to calculate with SQL

Use case

You have a table with the number of added lines of code and the number of defects. You need to calculate the quality of the code. The number of defects less than one defect per 10 lines indicates the good quality of the code. Otherwise the quality of the code is bad.

Solution

1. Switch the page to the edit mode.
2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.
3. Select the macro and click Edit.
4. In the Presets tab select Custom transformation and click Next.

5. Enter the following SQL query:

   SELECT
      *,
      (
         'Number of defects' * 10 / 'Lines of code added'
      )
      AS 'Defects per 10 lines',
      CASE
         WHEN
            (
               'Number of defects' * 10 / 'Lines of code added'
            )
            < 1 
         THEN
            "Good" 
         ELSE
            "Bad" 
      END
      AS 'Code quality' 
   FROM
      T1

   Copy

   SQL

   ('...' * 10 / '...')  AS '...'  calculates the number of defects per 10 lines of code and outputs the 'Defects per 10 lines' column. 

   CASE WHEN ... THEN ... ELSE ... END AS '...' goes through conditions and return a value when the first condition is met and outputs the 'Code quality' column.
6. Click Next.
7. Define the table settings and view options if needed.
8. Save the macro and the page.

You can use FORMATWIKI function for the purposes of cell formatting.