FREE AI-Enhanced Online SQLite Compiler

Untitled*

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

CREATE TABLE nobel_prizes ( A_YEAR INTEGER, CATEGORY TEXT, A_NAME TEXT, SOME_COLUMN TEXT );

INSERT INTO nobel_prizes(A_YEAR, CATEGORY, A_NAME, SOME_COLUMN) VALUES (2020, 'A', 'Name 1', 'some value 1');
INSERT INTO nobel_prizes(A_YEAR, CATEGORY, A_NAME, SOME_COLUMN) VALUES (2020, 'B', 'Name 2', 'some value 2');
INSERT INTO nobel_prizes(A_YEAR, CATEGORY, A_NAME, SOME_COLUMN) VALUES (2021, 'D', 'Name 1', 'some value 3'); 
INSERT INTO nobel_prizes(A_YEAR, CATEGORY, A_NAME, SOME_COLUMN) VALUES (2021, 'C', 'Name 3', 'some value 4'); 
INSERT INTO nobel_prizes(A_YEAR, CATEGORY, A_NAME, SOME_COLUMN) VALUES (2023, 'B', 'Name 2', 'some value 5'); 
INSERT INTO nobel_prizes(A_YEAR, CATEGORY, A_NAME, SOME_COLUMN) VALUES (2024, 'F', 'Name 1', 'some value 6');

SELECT * FROM (
WITH    -- C T E    p r i z e s :
  prizes AS
    ( Select  A_NAME, Count(*) as CNT
      From  nobel_prizes
      Group By A_NAME Having Count(*) = 2  -- <-- this excludes name 1 as tripple winner
    )
Select      np.A_YEAR, np.CATEGORY, np.A_NAME, p.CNT
From        nobel_prizes np
Inner Join  prizes p on p.A_NAME = np.A_NAME
Order By    np.A_NAME, np.A_YEAR
);

SELECT * FROM (
WITH    -- add years and categories using GROUP_CONCAT()
  prizes AS
    ( Select  A_NAME, Count(*) as CNT, GROUP_CONCAT(A_YEAR, ' | ') as YEARS, GROUP_CONCAT(CATEGORY, ' | ') as CATEGORIES
      From  nobel_prizes
      Group By A_NAME Having Count(*) > 1 )
Select      np.A_NAME, p.CNT, p.YEARS, p.CATEGORIES
From        nobel_prizes np
Inner Join  prizes p on p.A_NAME = np.A_NAME
Group By    np.A_NAME, p.CNT, p.YEARS, p.CATEGORIES
Order By    p.CNT Desc
 );

A_YEAR	CATEGORY	A_NAME	CNT
2020	B	Name 2	2
2023	B	Name 2	2

A_NAME	CNT	YEARS	CATEGORIES
Name 1	3	2020 \| 2021 \| 2024	A \| D \| F
Name 2	2	2020 \| 2023	B \| B