Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote -1 down vote favorite

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?

share|improve this question
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 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 at 19:23

closed as off topic by ypercube, Marian, RolandoMySQLDBA, Mat, dezso Mar 30 at 11:26

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined in the FAQ. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about closed questions here.

1 Answer

up vote 4 down vote accepted

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

share|improve this answer
Thank you! Yes, I forgot about UNION ALL... – SS_Rebelious Mar 29 at 20:04

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