Sum column as an additional column sql

Question

I am trying to work with a dataset that has distinct columns. I am looking at adding a column that provides sum of quantity against each row based on the Order ID. Please see below the input and desired output:

Input:

+----+---------+-----+----------+
| ID | OrderID | PID | Quantity |
+----+---------+-----+----------+
| 1  | 10248   | 11  | 12       |
+----+---------+-----+----------+
| 2  | 10248   | 42  | 10       |
+----+---------+-----+----------+
| 3  | 10248   | 72  | 5        |
+----+---------+-----+----------+
| 4  | 10249   | 14  | 9        |
+----+---------+-----+----------+
| 5  | 10249   | 51  | 40       |
+----+---------+-----+----------+
| 6  | 10250   | 41  | 10       |
+----+---------+-----+----------+
| 7  | 10250   | 51  | 35       |
+----+---------+-----+----------+
| 8  | 10250   | 65  | 15       |
+----+---------+-----+----------+
| 9  | 10251   | 22  | 6        |
+----+---------+-----+----------+
| 10 | 10251   | 57  | 15       |
+----+---------+-----+----------+
| 11 | 10251   | 65  | 20       |
+----+---------+-----+----------+
| 12 | 10252   | 20  | 40       |
+----+---------+-----+----------+
| 13 | 10252   | 33  | 25       |
+----+---------+-----+----------+

Desired output:

+----+---------+-----+----------+-------+
| ID | OrderID | PID | Quantity | Total |
+----+---------+-----+----------+-------+
| 1  | 10248   | 11  | 12       | 27    |
+----+---------+-----+----------+-------+
| 2  | 10248   | 42  | 10       | 27    |
+----+---------+-----+----------+-------+
| 3  | 10248   | 72  | 5        | 27    |
+----+---------+-----+----------+-------+
| 4  | 10249   | 14  | 9        | 49    |
+----+---------+-----+----------+-------+
| 5  | 10249   | 51  | 40       | 49    |
+----+---------+-----+----------+-------+
| 6  | 10250   | 41  | 10       | 60    |
+----+---------+-----+----------+-------+
| 7  | 10250   | 51  | 35       | 60    |
+----+---------+-----+----------+-------+
| 8  | 10250   | 65  | 15       | 60    |
+----+---------+-----+----------+-------+
| 9  | 10251   | 22  | 6        | 41    |
+----+---------+-----+----------+-------+
| 10 | 10251   | 57  | 15       | 41    |
+----+---------+-----+----------+-------+
| 11 | 10251   | 65  | 20       | 41    |
+----+---------+-----+----------+-------+
| 12 | 10252   | 20  | 40       | 65    |
+----+---------+-----+----------+-------+
| 13 | 10252   | 33  | 25       | 65    |
+----+---------+-----+----------+-------+

Answer 1

Since you didn't mention any RDBMS, this query below will almost work in most RDBMS. What the query does is it calculates the Total inside the subquery and joins the result in the original table itself.

SELECT  a.*,
        b.Total
FROM    TableName a
        INNER JOIN
        (
            SELECT  OrderID, SUM(Quantity) Total
            FROM    TableName
            GROUP   BY OrderID
        ) b ON a.OrderID = b.OrderID

Here's a Demo.

However, if your RDBMS supports window function such as SUM() OVER(), this can be simple written as

SELECT  a.*,
        SUM(Quantity) OVER (PARTITION BY OrderID) Total
FROM    TableName a

Here's a Demo.

Answer 2

Use GROUP BY.

Try:

SELECT OrderID, COUNT(quantity) as total FROM table GROUP BY OrderID;