How do I create a table whose values are based on queries of same table?

Question

This is something that interests me, but I can't seem to find an answer as to how to do it.

Basically, if I have a table and I want a column of that table to be based on a query, particularly when that query is based on information that is inside the same table, how do I do that?

I don't have access to my SQL program right now, but I suspect it would look something like this:

{
     CREATE TABLE ABCD as ABCE
     (

          A int(11) default null,
          B int(11) default sum(ABCE.A)
     );
}

or something to that effect.

Am I way off base?

Answer 1

You are not off-base at all. Actually pretty close. In t-sql you can just use code like this:

CREATE TABLE [dbo].[Sample2](
    [myIntColumn] [int] NULL,
    [myOtherIntColumn] [int] NOT NULL,
    [myBigIntColumn] AS ([myOtherIntColumn]+[myIntColumn])
)

GO

    INSERT INTO Sample2 VALUES (365,256),(10,25000), (1,2)

    SELECT * FROM Sample2

    --myIntColumn   myOtherIntColumn    myBigIntColumn
    --365           256                 621
    --10            25000               25010
    --1             2                   3

If you are looking for an aggregate as the default value, I do not believe you can do that simply. To have an aggregate as a column in the a table, a view is a viable option, like so:

CREATE VIEW Sample3
AS 
WITH CTE AS (
SELECT SUM(myIntColumn) as mySUM
From Sample2 )
SELECT Sample2.*, CTE.mySUM FROM Sample2, CTE
GO

SELECT * FROM Sample3

--myIntColumn   myOtherIntColumn    myBigIntColumn  mySUM
--365           256                 621             376
--10            25000               25010           376
--1             2                   3               376

These queries assume your platform is MS SQL Server.

If your tag sql was meant to only indicate the language, it would be helpful both for this question and future questions to tag with the platform. For example, you could additionally tag as sql-server.

Depending on your RBDMS and your requirements you might be able to leverage an indexed/materialized view. This could additionally help performance, but there are some limitations on an Indexed View in SQL Server.

Answer 2

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 ;