How can I compare multiple data between two periods?-CodePudding

I am trying to compare the growth rate of some sale data, say, for example, a comparison between data from 2021 and 2022, or between any date really, this is what I am getting:

Schema (SQLite v3.39)

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

Query #1

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);

There are no results to be displayed.

Query #2

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;

code	qty	sale
LUIGI	8	16
MARIO	0	0
PEACH	-4	-8

View on DB Fiddle

this is the result I expect:

code	qty	qty2	sale	sale2
LUIGI	2	2	4.00	4.00
MARIO	0	0	0	0
PEACH	-1	0	-2.00	0

note: I noticed my WHERE CLAUSE is acting weirdly and also noticed it's using the entire sum instead of taking the where cluase into account (no solutions so far).

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.