Some SQL

Some SQL.md

DDL

CREATE TABLE PRACTICE (
    ID      INTEGER      PRIMARY KEY AUTOINCREMENT,
    [GROUP] VARCHAR (30) NOT NULL
                         DEFAULT ('DEFAULT'),
    LABEL   VARCHAR (30) NOT NULL,
    VALUE   INTEGER      NOT NULL
    );

Data

INSERT INTO PRACTICE
([GROUP], LABEL, VALUE)
VALUES 
("ALPHA", "NORMAL", 3),
("ALPHA", "NORMAL", 3),
("ALPHA", "VIP", 3),
("BETA", "NORMAL", 4),
("BETA", "NORMAL", 4),
("BETA", "VIP", 4),
("BETA", "VIP", 5),
("GAMMA", "NORMAL", 6);

Query

• 原則としてGROUPプロパティごとに集計するがLABEL = 'VIP'のときはVIPグループとして扱う

SELECT
SUM(VALUE) AS SUM,
CASE WHEN LABEL = 'VIP' THEN 'VIP' ELSE [GROUP] END AS ALT_GROUP
FROM PRACTICE
GROUP BY ALT_GROUP;

Result

SUM	ALT_GROUP
6	ALPHA
8	BETA
6	GAMMA
12	VIP

懸念

• LABEL = 'VIP'のときに置き換える値がGROUPのなかに存在しないことが保証されない

Query 2

SELECT
SUM(VALUE) AS SUM,
CASE WHEN LABEL IN ('VIP') THEN (LABEL || '-' || [GROUP]) ELSE [GROUP] END AS ALT_GROUP
FROM PRACTICE
GROUP BY ALT_GROUP;