In MySQL, the code that Thronk provided in his answer, will have to modified because of MySQL's limitations: there are no CTEs and views should have no derived tables:
create table abcd
(a int primary key,
b int) ;
This will give an error:
create view sample2
as
select abcd.a, abcd.b,
s.mysum
from abcd
cross join
(select sum(b) as mysum from abcd) as s ;
> ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
A workaround is to not use a derived table but first join and then group by the table's PK:
create view sample3
as
select abcd.a, abcd.b,
sum(s.b) as mysum
from abcd
cross join abcd as s
group by abcd.a ; -- the GROUP should be BY the table's PK
A simple test with:
insert into abcd
(a, b)
values
(1, 10),
(2, 7),
(3, 7),
(4, 12) ;
select * from sample3 ;
will give you:
+---+------+-------+
| a | b | mysum |
+---+------+-------+
| 1 | 10 | 36 |
| 2 | 7 | 36 |
| 3 | 7 | 36 |
| 4 | 12 | 36 |
+---+------+-------+
However, the above workaround will not be very efficient. Another approach, which will probably be much better is to define 2 views. One to calculate the aggregates (sum, avg, whatever) and then use the first view to define the second. Notice the similarity between views sample5
below and the (failed attempt) sample2
above:
create view sample4
as
select sum(b) as mysum
from abcd ;
create view sample5
as
select abcd.a, abcd.b,
s.mysum
from abcd
cross join
sample4 as s ;