So I have this sample data:

name  | quantity
bike1 | 1
bike2 | 2
bike3 | 3

I want to transform it into:

name  | sum_quantity
bike1 | 6
bike2 | 6
bike3 | 6

Basically want to take the sum of all quantity and set it for each row in the column.

CodePudding user response：

In MySQL you can use a CROSS JOIN with the SUM

CREATE TABLE bikes (
  `name` VARCHAR(5),
  `quantity` INTEGER
);

INSERT INTO bikes
  (`name`, `quantity`)
VALUES
  ('bike1', '1'),
  ('bike2', '2'),
  ('bike3', '3');

SELECT b.name, sum_quantity FROM bikes b CROSS JOIN (SELECT SUM(`quantity`) sum_quantity FROM bikes) t1

name  | sum_quantity
:---- | -----------:
bike1 |            6
bike2 |            6
bike3 |            6

db<>fiddle here

In Postgres it is the same

CREATE TABLE bikes (
  "name" VARCHAR(5),
  "quantity" INTEGER
);

INSERT INTO bikes
  ("name", "quantity")
VALUES
  ('bike1', '1'),
  ('bike2', '2'),
  ('bike3', '3');

SELECT b.name, sum_quantity FROM bikes b CROSS JOIN (SELECT SUM("quantity") sum_quantity FROM bikes) t1

name  | sum_quantity
:---- | -----------:
bike1 |            6
bike2 |            6
bike3 |            6

db<>fiddle here

CodePudding user response：

That's what sum in its analytic form is to be used for:

select "name", 
   sum("quantity") over () as sum_quantity
from bikes;