CODEWITHSUNDEEP
javascriptjquerypostgresqlsum
Carlos.V
Carlos.V

Reputation: 446

Sum in rows some other column rows values grouped by other column ID

Is it possible to do a query like this? I have the ID, some IN value, Out value and the last column TOTAL is the sum of the difference between IN and OUT. I just really need the complete sum. The ID2 rows that are equals must be summed in the SUM column. There is no ID2 quantity fixed, some times it would be just one row to sum, other cases there will be more. I could also add a difference column, to just sum the value in that row for the same ID2.

My doubt here is, is it possible to sum in a row, the accumulated in some rows at other columns? 

ID  ID2 IN  OUT TOTAL (IN-OUT)
1   2   200 150 blank-null-space-or whatever but a character
2   2   350 250 doesn't matter, also it can have the same SUM result as below
3   2   600 400 350 ( = 200 - 150 + 350 - 250 + 600 - 400)
4   3   100  80  20

Or I have to do it at the client side in JavaScript? Which is better or you do suggest? Maybe it is easier in JavaScript but I wanted to do it all in one step to reduce the pool connections.

A simple verifiable example would be

CREATE TABLE "table1" (
  "ID" int NOT NULL,
  "ID2" int DEFAULT NULL,
  "`EXT_ID" int DEFAULT NULL,
  "IN" int DEFAULT NULL
  "OUT" int DEFAULT NULL
);

INSERT INTO "table1" ("ID", "ID2", "EXT_ID", "IN", "OUT") VALUES
(1, 2, 1, 200, 150),
(2, 2, 2, 350, 250),
(3, 2, 2, 600, 400),
(4, 3, 4, 100,  80),
(5, 3, 5, 130, 100),
(6, 4, 6, 100,  80),
(7, 4, 7, 120,  70),
(8, 4, 7, 150,  90);

EDIT: Added an foreign ID column "EXT_ID" that have a condition show only one row and the one that has MAX value from the same "ID2".

Corrected SQL syntax.

Upvotes: 0

Views: 224

Answers (2)

Emilio Platzer
Emilio Platzer

Reputation: 2469

Edited

Yes, it is posible. You can use group by (if you only want the total)

CREATE TABLE "table1" (
  "ID" int NOT NULL,
  "ID2" int DEFAULT NULL,
  "EXT_ID" int DEFAULT NULL,
  "IN" int DEFAULT NULL,
  "OUT" int DEFAULT NULL
);

INSERT INTO "table1" ("ID", "ID2", "EXT_ID", "IN", "OUT") VALUES
(1, 2, 1, 200, 150),
(2, 2, 2, 350, 250),
(3, 2, 2, 600, 400),
(4, 3, 4, 100,  80),
(5, 3, 5, 130, 100),
(6, 4, 6, 100,  80),
(7, 4, 7, 120,  70),
(8, 4, 7, 150,  90);

select "ID2", sum("IN" - "OUT")
  from "table1"
  group by "ID2";

result 1

Old answer:

Yes, it is posible. You can use window functions

CREATE TABLE "table1" (
  "ID" int NOT NULL,
  "ID2" int DEFAULT NULL,
  "IN" int DEFAULT NULL,
  "OUT" int DEFAULT NULL
);

INSERT INTO "table1" ("ID", "ID2", "IN", "OUT") VALUES
(1, 2, 200, 150),
(2, 2, 350, 250),
(3, 2, 600, 400),
(4, 3, 100,  80);

select * , sum("IN" - "OUT") over (partition by "ID2" order by "ID")
  from "table1"
  order by "ID2", "ID";

result 2

As you see in https://www.postgresql.org/docs/current/static/functions-window.html

A functional example: http://rextester.com/OVCTC74070

Upvotes: 1

Kadice
Kadice

Reputation: 51

try this?

select (SUM(`IN` - `OUT`)) as 'Total' 
from (
  select * 
  from table1
  where ID2 IN (select ID2 from table1 where ID = "3")
) as temptable;

but this can only get result of 1 row, you might have to edit it.

Upvotes: 1

Related Questions