Select aggregate with respect to each different column separately

Question

I wanted to select each unique title, along with the related link. I want to choose the sum(price) with respect to title and sum(price) with respect to link in each record. This is my table structure:

title	link	price
a	1	5
a	1	10
b	2	15
c	2	20

Expected output:

link	title	title_price	link_price
1	a	15	15
2	b	15	35
2	c	20	35

Here's my attempt:

"SELECT link as link, title, sum(price) FROM `test` group by link,title"

This was tried in order to select the sum with respect to title but I also wanted to select sum with respect to link too in each row. So I use following query but it gives error:

SELECT t1.link as link, title, sum(price) as title_price,
(select link_price from(
(select link, sum(price) as link_price from `test` group by link where link=t1.link)
))
as price
FROM `test` as t1 group by link,title

Answer 1

You could use sum aggregate function to find the sum for each title, link group, then use sum window function to find the sum for each link group.

SELECT link, title, sum_per_link_title AS title_price,
  SUM(sum_per_link_title) OVER (PARTITION BY link) AS link_price
FROM
(
  SELECT title, link, SUM(price) AS sum_per_link_title
  FROM test
  GROUP BY title, link
) T
ORDER BY link, title

See a demo.