Sum with Loop expression

Question

I want to make a calculation over several lines and as a result there should be one result

Table g

| id |Oppervlakte|
| 1  | 10        |
| 2  |12         |
| 3  | 7         |
| 4  | 8         |

table d

| id | gid | Oppervlakte |
| 1  | 1   | 2           |
| 1  | 2   | 3           |
| 1  | 2   | 2           |
| 1  | 2   | 2           |
| 1  | 3   | 1           |

And the result

| id |test |
| 1  | 216 |

my code now is

Select r.id,
sum((g.oppervlakte-sum(d.oppervlakte)*8) as 'test' 
from r 
join g on g.rid=r.id 
join d on d.gid=g.id 
join c on c.id = g.Id 
where c.cType=0 
Group by r.id, g.oppervlakte

Answer 1

You probably just want to aggregate twice, once in a sub-query...

SELECT
  r.id,
  SUM((g.oppervlakte - d.oppervlakte) * 8) as 'test' 
FROM
  r 
INNER JOIN
  g
    ON g.rid = r.id
INNER JOIN
(
  SELECT
    d.gid,
    SUM(d.oppervlakte)   AS oppervlakte
  FROM
    d
  GROUP BY
    d.gid
)
  d
    ON d.gid = g.id 
INNER JOIN
  c
    ON c.id = g.id
WHERE
  c.cType = 0
GROUP BY
  r.id