CODEWITHSUNDEEP
sqlhiveaggregation
nene momo
nene momo

Reputation: 1

Add a summary column with totals

I would like to make a query and add a summary column of a total to a single table (b) from the raw data in table (a) using SQL.

Please refer to the example. Table b is the results of query I want.

Anyone could help on this? Thank you.

example

Upvotes: 0

Views: 462

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You want window functions:

select user, restaurant, sum(spending) as spending,
       sum(sum(spending)) over () as total_spending
from t
group by user, restaurant;

You can use create table as or insert if you want to insert this into a new table.

Upvotes: 0

ShengHow95
ShengHow95

Reputation: 246

Try using Join, based on your example, lets call it test:

Select table1.user, table1.restaurant, table1.spendings, table2.total_spendings
FROM
    (
        SELECT user, restaurant, sum(spending) AS spendings
        FROM test
        GROUP BY user, restaurant
    ) table1
JOIN
    (
        SELECT user, sum(spending) AS total_spendings
        FROM test
        GROUP BY user
    ) table2
ON table1.user = table2.user;

Upvotes: 1

JKC
JKC

Reputation: 47

Try creating a view (virtual table) instead - Code for creating a view

CREATE VIEW VIEW_NAME
AS SELECT COUNT(*)
FROM TABLE_A;

Upvotes: 0

Related Questions