sql sum per day

Raw

SQL: sum per day

CREATE TABLE "stat" (
    "id" int4 NOT NULL DEFAULT nextval('stat_id_seq'::regclass),
    "type" varchar(100),
    "datetime" timestamptz,
    "amount" int8,
    "wallet_id" int8,
    PRIMARY KEY ("id")
);

id	type	datetime	amount	wallet_id
1	deposit	2023-06-16 14:33:56.962547+00	1	1
2	deposit	2023-06-16 14:35:56.962547+00	1	1
3	deposit	2023-06-16 14:36:56.962547+00	1	1
4	deposit	2023-06-17 14:33:56.962547+00	1	1
5	deposit	2023-06-18 14:33:56.962547+00	1	1
6	deposit	2023-06-19 00:00:00.962547+00	1	1
7	deposit	2023-06-20 14:30:56.962547+00	1	1
8	deposit	2023-06-20 14:31:56.962547+00	1	1
9	deposit	2023-06-20 14:32:56.962547+00	1	1
10	deposit	2023-06-20 14:33:56.962547+00	1	1

SELECT CAST(datetime as date) as day, SUM(stat.amount) as amount_per_day
FROM stat
WHERE CAST(datetime as date) BETWEEN '2023-06-16' AND '2023-06-17'
AND wallet_id = 1
GROUP BY day
ORDER BY day;

Raw

sql_sum_per_day_v2_.md

    CREATE TABLE "stat" (
    "id" int4 NOT NULL DEFAULT nextval('stat_id_seq'::regclass),
    "type" varchar(100),
    "datetime" timestamptz,
    "amount" int8,
    "wallet_id" int8,
    "date" date DEFAULT now(),
    PRIMARY KEY ("id")
);


id
type
datetime
amount
wallet_id
date


1
deposit
2023-06-16 14:33:56.962547+00
1
1
2023-06-16

2
deposit
2023-06-16 14:35:56.962547+00
1
1
2023-06-16

3
deposit
2023-06-16 14:36:56.962547+00
1
1
2023-06-16

4
deposit
2023-06-17 14:33:56.962547+00
1
1
2023-06-17

5
deposit
2023-06-18 14:33:56.962547+00
1
1
2023-06-18

6
deposit
2023-06-19 00:00:00.962547+00
1
1
2023-06-19

7
deposit
2023-06-20 14:30:56.962547+00
1
1
2023-06-20

8
deposit
2023-06-20 14:31:56.962547+00
1
1
2023-06-20

9
deposit
2023-06-20 14:32:56.962547+00
1
1
2023-06-20

10
deposit
2023-06-20 14:33:56.962547+00
1
1
2023-06-20

SELECT date as day, SUM(stat.amount) as amount_per_day
FROM stat
WHERE date BETWEEN '2023-06-16' AND '2023-06-17'
AND wallet_id = 1
GROUP BY day
ORDER BY day;

  

id	type	datetime	amount	wallet_id	date
1	deposit	2023-06-16 14:33:56.962547+00	1	1	2023-06-16
2	deposit	2023-06-16 14:35:56.962547+00	1	1	2023-06-16
3	deposit	2023-06-16 14:36:56.962547+00	1	1	2023-06-16
4	deposit	2023-06-17 14:33:56.962547+00	1	1	2023-06-17
5	deposit	2023-06-18 14:33:56.962547+00	1	1	2023-06-18
6	deposit	2023-06-19 00:00:00.962547+00	1	1	2023-06-19
7	deposit	2023-06-20 14:30:56.962547+00	1	1	2023-06-20
8	deposit	2023-06-20 14:31:56.962547+00	1	1	2023-06-20
9	deposit	2023-06-20 14:32:56.962547+00	1	1	2023-06-20
10	deposit	2023-06-20 14:33:56.962547+00	1	1	2023-06-20

Raw

sql_sum_per_day_v3_in_usd.md

    CREATE TABLE "public"."stat" (
    "id" int4 NOT NULL DEFAULT nextval('stat_id_seq'::regclass),
    "type" varchar(100),
    "datetime" timestamptz,
    "amount" int8,
    "wallet_id" int8,
    "date" date DEFAULT now(),
    "symbol" varchar(10),
    PRIMARY KEY ("id")
);


id
type
datetime
amount
wallet_id
date
symbol


1
deposit
2023-06-16 14:33:56.962547+00
1
1
2023-06-16
trx

2
deposit
2023-06-16 14:35:56.962547+00
1
1
2023-06-16
trx

3
deposit
2023-06-16 14:36:56.962547+00
1
1
2023-06-16
trx

4
deposit
2023-06-17 14:33:56.962547+00
1
1
2023-06-17
trx

5
deposit
2023-06-18 14:33:56.962547+00
1
1
2023-06-18
trx

6
deposit
2023-06-19 00:00:00.962547+00
1
1
2023-06-19
trx

7
deposit
2023-06-20 14:30:56.962547+00
1
1
2023-06-20
trx

8
deposit
2023-06-20 14:31:56.962547+00
1
1
2023-06-20
trx

13
deposit
2023-06-20 14:33:56.962547+00
1
1
2023-06-20
btc

14
deposit
2023-06-20 14:33:56.962547+00
1
1
2023-06-20
btc

SELECT date AS day, SUM(stat.amount) * 0.99985 AS amount_per_day_in_usd
FROM stat
WHERE date BETWEEN '2023-06-16' AND '2023-06-17'
AND wallet_id = 1
AND symbol = 'trx'
GROUP BY day
ORDER BY day;

  

zmts/sql_sum_per_day_v1_with_cast.md

SQL: sum per day