SQL how to get total sum from all product rows without losing each product row from the view

I need to retrieve the total sum of all rows from this view, if I group by orderid the sum function works but then I lose each product row from the view and I don’t want that in this case. Is there a way to print the total sum (duplicated) for each row?

View:

DROP VIEW IF EXISTS v_invoice;

CREATE VIEW v_invoice AS
SELECT
    f.id AS "Invoice nr",
    b.id AS "Order id",
    b.kundid AS "Customer id",
    p2o.produktid AS "Product id",
    p.namn AS "Name",
    p2o.antal AS "Number",
    p.pris AS "Price",
    (p2o.antal * p.pris) AS "Price per row",
    SUM(p2o.antal * p.pris) AS "Totalsum",
    b.skickad AS "Shipped",
    f.betald AS "Paid"
FROM
    faktura as f
    LEFT OUTER JOIN bestallning AS b ON f.orderid = b.id
    LEFT OUTER JOIN produkt2order AS p2o ON f.orderid = p2o.orderid
    LEFT OUTER JOIN produkt AS p ON p2o.produktid = p.produktid
GROUP BY
    p2o.produktid;

Table printed:

MySQL [eshop]> SELECT * FROM v_invoice;
+------------+----------+-------------+------------+----------------------------+--------+-------+---------------+----------+---------------------+------------+
| Invoice nr | Order id | Customer id | Product id | Name                       | Number | Price | Price per row | Totalsum | Shipped             | Paid       |
+------------+----------+-------------+------------+----------------------------+--------+-------+---------------+----------+---------------------+------------+
|          1 |        1 |           1 | kaffe1     | Kaffemugg med dbwebb-tryck |      2 |    69 |           138 |      138 | 2020-03-20 19:41:05 | 2020-03-20 |
|          1 |        1 |           1 | te1        | Temugg med dbwebb-tryck    |      2 |    79 |           158 |      158 | 2020-03-20 19:41:05 | 2020-03-20 |
+------------+----------+-------------+------------+----------------------------+--------+-------+---------------+----------+---------------------+------------+
2 rows in set (0.001 sec)

Answer

You can use the WITH ROLLUP modifier to GROUP BY to get a summary row. However, because you have an invalid GROUP BY clause (not all of the non-aggregated columns are in the GROUP BY clause), you will get indeterminate values for the other columns in the ROLLUP summary line. However, p2o.produktid will be NULL so you can use that to blank the other values. For example:

CREATE VIEW v_invoice AS
SELECT
    IF(p2o.produktid IS NULL, NULL, f.id) AS "Invoice nr",
    IF(p2o.produktid IS NULL, NULL, b.id) AS "Order id",
    IF(p2o.produktid IS NULL, NULL, b.kundid) AS "Customer id",
    p2o.produktid AS "Product id",
    IF(p2o.produktid IS NULL, NULL, p.namn) AS "Name",
    IF(p2o.produktid IS NULL, NULL, p2o.antal) AS "Number",
    IF(p2o.produktid IS NULL, NULL, p.pris) AS "Price",
    IF(p2o.produktid IS NULL, NULL, (p2o.antal * p.pris)) AS "Price per row",
    SUM(p2o.antal * p.pris) AS "Totalsum",
    IF(p2o.produktid IS NULL, NULL, b.skickad) AS "Shipped",
    IF(p2o.produktid IS NULL, NULL, f.betald) AS "Paid"
FROM
    faktura as f
LEFT OUTER JOIN bestallning AS b ON f.orderid = b.id
LEFT OUTER JOIN produkt2order AS p2o ON f.orderid = p2o.orderid
LEFT OUTER JOIN produkt AS p ON p2o.produktid = p.produktid
GROUP BY
    p2o.produktid WITH ROLLUP;

Note that because your GROUP BY doesn’t include all non-aggregated columns, the values you see in the VIEW for those columns will be indeterminate, and may be different each time you SELECT * FROM v_invoice.