CODEWITHSUNDEEP
sqlpostgresqlview
Shield-pro
Shield-pro

Reputation: 21

How to I create column in view

I am trying to collect count from two unrelated tables and represent them as a View, how do I put both values in different columns which I'll create on fly? I was trying something like this

 CREATE VIEW entries AS
   SELECT count(*)
     FROM p as newEntries
 UNION ALL
   SELECT count(*)
     FROM s as totalEntries

Upvotes: 0

Views: 46

Answers (2)

Littlefoot
Littlefoot

Reputation: 142705

Tim's answer answers your question.

However, your attempt isn't that bad (from my point of view) as I use it rather frequently, when comparing number of rows in different tables (for example, during migration process where I have to check bunch of tables at once).

Here's how: add yet another column, which shows the source of COUNT function:

SQL> create or replace view v_count as
  2  select 'EMP' source, count(*) cnt from emp
  3  union all
  4  select 'DEPT', count(*) from dept;

View created.

SQL> select * from v_count where source = 'EMP';

SOUR        CNT
---- ----------
EMP          14

SQL> select * from v_count;

SOUR        CNT
---- ----------
EMP          14
DEPT          4

SQL>

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

You could just list both count queries as subqueries:

CREATE VIEW entries AS
    (SELECT COUNT(*) FROM p) AS newEntries,
    (SELECT COUNT(*) FROM s) AS totalEntries
FROM dual

Upvotes: 1

Related Questions