FREE AI-Enhanced Online SQLite Compiler

Untitled*

You must execute the code again to save those values in the database.

-- Create table for test groups
CREATE TABLE test_groups (
    name TEXT NOT NULL UNIQUE,
    test_value INTEGER NOT NULL
);

-- Create table for test cases
CREATE TABLE test_cases (
    id INTEGER PRIMARY KEY,
    group_name TEXT NOT NULL,
    status TEXT NOT NULL
);

-- Insert data into test_groups
INSERT INTO test_groups (name, test_value)
VALUES
  ('performance', 15),
  ('corner cases', 10), 
  ('numerical stability', 20), 
  ('memory usage', 10);

-- Insert data into test_cases
INSERT INTO test_cases (id, group_name, status)
VALUES 
  (13, 'memory usage', 'OK'),
  (14, 'numerical stability', 'OK'),
  (15, 'memory usage', 'ERROR'),
  (16, 'numerical stability', 'OK'),
  (17, 'numerical stability', 'OK'),
  (18, 'performance', 'ERROR'),
  (19, 'performance', 'ERROR'),
  (20, 'memory usage', 'OK'),
  (21, 'numerical stability', 'OK');

--To generate expected output
Select t.name, COALESCE(all_test_cases,0) AS 'all_test_cases',
COALESCE(passed_test_cases,0) AS 'passed_test_cases',
COALESCE(test_value,0) AS 'total_value'
FROM (
  select tg.name, tg.test_value, gt.all_test_cases, gt.passed_test_cases from test_groups tg 
  LEFT JOIN
(Select group_name,Count(*) as 'all_test_cases', Count(CASE WHEN status='OK' THEN 1 END)as 'passed_test_cases'
FROM test_cases
Group By group_name)as gt ON gt.group_name = tg.name) as t ORDER BY total_value ASC;

name	all_test_cases	passed_test_cases	total_value
corner cases	0	0	10
memory usage	3	2	10
performance	2	0	15
numerical stability	4	4	20