I am trying to compare the growth rate of sale data between dates.

Schema (SQLite v3.39):

CREATE TABLE "VENTAS" (
    "date" TEXT,
    "code"  TEXT,
    "qty"   REAL,
    "cost"  REAL,
    "price" REAL
);

INSERT INTO "VENTAS" VALUES
("2022-01-01","MARIO", 1, 1.00, 2.00),
("2022-01-05","MARIO", -1, -1.00, -2.00),
("2022-01-09","LUIGI", 1, 1.00, 2.00),
("2022-01-23","LUIGI", 1, 1.00, 2.00),
("2022-01-30","PEACH", -1, -1.00, -2.00),
("2022-02-01","MARIO", 1, 1.00, 2.00),
("2022-02-11","MARIO", -1, -1.00, -2.00),
("2022-02-19","LUIGI", 1, 1.00, 2.00),
("2022-02-28","LUIGI", 1, 1.00, 2.00),
("2022-03-01","PEACH", -1, -1.00, -2.00),
("2022-03-15","MARIO", 1, 1.00, 2.00),
("2022-03-20","MARIO", -1, -1.00, -2.00),
("2022-03-29","LUIGI", 1, 1.00, 2.00),
("2022-04-09","LUIGI", 1, 1.00, 2.00),
("2022-04-12","PEACH", -1, -1.00, -2.00),
("2022-04-18","MARIO", 1, 1.00, 2.00),
("2022-04-22","MARIO", -1, -1.00, -2.00),
("2022-04-22","LUIGI", 1, 1.00, 2.00),
("2022-05-13","LUIGI", 1, 1.00, 2.00),
("2022-05-25","PEACH", -1, -1.00, -2.00);

SELECT code,
(SELECT SUM(qty) WHERE date BETWEEN '2022-01-01' AND '2022-01-31') as qty,
(SELECT SUM(qty) WHERE date BETWEEN '2022-02-01' AND '2022-02-28') as qty2,
(SELECT SUM((price * ABS(qty))) WHERE date BETWEEN '2022-01-01' AND '2022-01-31') as sale,
(SELECT SUM((price * ABS(qty))) WHERE date BETWEEN '2022-02-01' AND '2022-02-28') as sale2
FROM VENTAS
WHERE qty != 0
GROUP BY code;

Result:

DB Fiddle

I expect:

It is using entire sum instead of conforming to where clause.

CodePudding user response：

Use conditional aggregation:

SELECT code,
       TOTAL(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-31' THEN qty END) AS qty,
       TOTAL(CASE WHEN date BETWEEN '2022-02-01' AND '2022-02-28' THEN qty END) AS qty2,
       TOTAL(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-31' THEN price * ABS(qty) END) AS sale,
       TOTAL(CASE WHEN date BETWEEN '2022-02-01' AND '2022-02-28' THEN price * ABS(qty) END) AS sale2
FROM VENTAS
WHERE qty <> 0
GROUP BY code;

See the demo.

CodePudding user response：

You can do a self-join on the table using the BETWEEN operator on the dates.