Creating a view (virtual table) which display colums with particular requirements

So I`m trying to make a statistic view which counts various things like how many users are in table called “PACIENTAS” , how many doctors are in table “GYDYTOJAS” and it works.

   CREATE OR REPLACE FORCE VIEW "NEW" ("PACIENTU SKAIČIUS DUOMENŲ BAZĖJE", "PACIENTAI    UŽSIREGISTRAVĘ VIZITUI", "LIGONINIU SKAICIUS", "GYDYTOJU SKAIČIUS", "ŠIO MENESIO REGISTRACIJOS")   AS 
   SELECT(
   SELECT COUNT(*) FROM   PACIENTAS) AS VISIPACIENTAI,
  (SELECT COUNT(*) FROM   REGISTRACIJA) AS REGPACIENTAI,
  (SELECT COUNT(*) FROM  LIGONINE) AS VISOSLIGONINES,
  (SELECT COUNT(*) FROM  GYDYTOJAS) AS VISIGYDYTOJAI,
  (SELECT COUNT(*) FROM  REGISTRACIJA)  AS REGISTRACIJA
  FROM dual
  /

Counts how many columns are in table

But I want to count only columns that fulfil a specified condition

   CREATE OR REPLACE FORCE VIEW "NEW" ("PACIENTU SKAIČIUS DUOMENŲ BAZĖJE", "PACIENTAI   UŽSIREGISTRAVĘ VIZITUI", "LIGONINIU SKAICIUS", "GYDYTOJU SKAIČIUS", "ŠIO MENESIO REGISTRACIJOS") AS  
   SELECT(
   SELECT COUNT(*) FROM   PACIENTAS) AS VISIPACIENTAI,
   (SELECT COUNT(*) FROM   REGISTRACIJA) AS REGPACIENTAI,
   (SELECT COUNT(*) FROM  LIGONINE) AS VISOSLIGONINES,
   (SELECT COUNT(*) FROM  GYDYTOJAS) AS VISIGYDYTOJAI,
   (SELECT COUNT(*) FROM  REGISTRACIJA)  AS REGISTRACIJA
   FROM dual
   WHERE ASMENSKODAS > 10  //ASMENSKODAS is a column from PACIENTAS TABLE
   /

When I write this code I get an error

ORA-00904: “PACIENTAS”: invalid identifier

I thinking maybe I need to write my condition in count parentheses but I don’t know how, I need ideas because I am stuck.

Answer

Do it like this

CREATE OR REPLACE FORCE VIEW "NEW" ("PACIENTU SKAIČIUS DUOMENŲ BAZĖJE", "PACIENTAI   UŽSIREGISTRAVĘ VIZITUI", "LIGONINIU SKAICIUS", "GYDYTOJU SKAIČIUS", "ŠIO MENESIO REGISTRACIJOS") AS  
   SELECT(
   SELECT COUNT(*) FROM   PACIENTAS WHERE ASMENSKODAS > 10) AS VISIPACIENTAI,
   (SELECT COUNT(*) FROM   REGISTRACIJA) AS REGPACIENTAI,
   (SELECT COUNT(*) FROM  LIGONINE) AS VISOSLIGONINES,
   (SELECT COUNT(*) FROM  GYDYTOJAS) AS VISIGYDYTOJAI,
   (SELECT COUNT(*) FROM  REGISTRACIJA)  AS REGISTRACIJA
   FROM dual
     //ASMENSKODAS is a column from PACIENTAS TABLE
   /