Here is our data:
Here is the output that we want:
|total for a||9|
|total for b||14|
One way to do an insert in SQL would be to use table generating functions, like those built into Hive. However, many SQL databases don’t have any of these functions built in. Amazon Redshift is unlikely to ever support explode() or any similar functions.
The natural way to insert rows though, is to use standard SQL and a Cartesian join. Although all joins in SQL are Cartesian joins, the term is often reserved for a join that utilizes a Cartesian product to get a set that is larger than any of the sets being joined.
Often, a Cartesian join is unintended, and can cause duplicate rows if the join predicate matches multiple rows because the key being joined on is not unique.
To do a Cartesian join to add rows above your results, you would create a helper table like this:
Then use SQL like this:
SELECT CASE WHEN b.uniq_id = 0 THEN 'total for ' ||a.product ELSE a.product END AS product, CASE WHEN b.uniq_id = 0 THEN a.total ELSE a.num_sold END AS num_sold FROM (SELECT *, SUM(1) OVER (PARTITION BY product ORDER BY product ROWS UNBOUNDED PRECEDING) AS row_count, SUM(num_sold) OVER (PARTITION BY product ORDER BY product ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total FROM product_sold_table) a LEFT JOIN helper_table b ON (a.row_count = b.row_count) ORDER BY a.product, COALESCE(b.uniq_id,1), a.num_sold