How to I create column in view

Question

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

Tim Biegeleisen · Accepted Answer · 2018-03-08 01:21:52Z

1

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

answered Mar 8, 2018 at 1:21

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Littlefoot · Accepted Answer · 2018-03-08 06:13:42Z

0

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>

answered Mar 8, 2018 at 6:13

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Collectives™ on Stack Overflow

How to I create column in view

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related