I use PostgreSQL 13. I have several tables. And I want to make a select-request, that get the names of these tables and the number of lines with an NULL updated_date
-column.
I can output the number of lines with an NULL date. I do this:
select count(*)
from TABLE_1
where updated_date is null
UNION
select count(*)
from TABLE_2
where updated_date is null;
-------
count |
-------
1 |
2 |
-------
I can get the names of the tables like this:
select t.tableoid::regclass as table_name
from TABLE_1 t
where updated_date is null;
------------
table_name |
------------
TABLE_1 |
------------
select t.tableoid::regclass as table_name
from TABLE_2 t
where updated_date is null;
------------
table_name |
------------
TABLE_2 |
TABLE_2 |
------------
Finally, I need a SQL-request, that will give to me a result:
--------------------
table_name | count |
--------------------
TABLE_1 | 1 |
TABLE_2 | 2 |
--------------------
I try to do this:
select t1.tableoid::regclass, count(*)
from TABLE_1 t1
where updated_date is null
UNION
select t2.tableoid::regclass, count(*)
from TABLE_2 t2
where updated_date is null;
but I get the mistake:
[42803] ERROR: column "t1.tableoid" must appear in the GROUP BY clause or be used in an aggregate function
How can I fix this?
Seems you need something like:
select t1.tableoid::regclass t_name, count(*) t_count
from TABLE_1 t1
where t1.updated_date is null
group by t_name
union
select t2.tableoid::regclass t_name, count(*) t_count
from TABLE_2 t2
where t2.updated_date is null
group by t_name;