-1

I'm trying to create view that will summarize data in two tables. Each table have several columns, but they both have NAME and AREA columns. And I want these two columns to be united correspondingly in view. I tried to create view using following query:

CREATE VIEW summary AS
INSERT INTO (SELECT Name, SUM(Area) FROM table1 GROUP BY Name)
SELECT *
FROM (SELECT Name, SUM(Area) FROM table2 GROUP BY Name)

But I get the error: SQL error: near "INSERT": syntax error. Actually I tried different querys involving INSERT INTO and it seems that CREATE VIEW will not work with INSERT INTO, and INSERT INTO does not accept subquery - only existing table (even temporal) would do.

How to rewrite CREATE VIEW statement to achieve my goal?

  • 1
    You probably need to use UNION or UNION ALL, not INSERT: CREATE VIEW summary AS SELECT Name, SUM(Area) FROM table1 GROUP BY Name UNION ALL SELECT Name, SUM(Area) FROM table2 GROUP BY Name ; – ypercubeᵀᴹ Mar 29 '13 at 19:14
  • 2
    INSERT is not used in a view create statement in any dialect of SQL I have heard of - a VIEW is (as the name implies) intended to display data only, not for DML operations. – JNK Mar 29 '13 at 19:23
4

I guess you want to use UNION or UNION ALL, not INSERT:

CREATE VIEW summary AS 
    SELECT Name, SUM(Area) AS Area 
    FROM table1 
    GROUP BY Name 
  UNION ALL 
    SELECT Name, SUM(Area) 
    FROM table2 
    GROUP BY Name ;

If there are values in the Name columns that appear in both tables and you want them summarized in one row, you can use this:

CREATE VIEW summary3 AS 
   SELECT Name, SUM(Area) AS Area 
   FROM summary 
   GROUP BY Name ;

Tested at SQL-Fiddle

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.