Hi
I am trying to get the count(*) from a subquery which has a column count(*) . The result of outer query according to me should be 1, but it takes the count(*) from the inner query and results the inner query's count(*). Is this known/correct behaviour.
-- Inner Query Output.
dbadmin=> select 'No of rows' as description , count(*) from tables;
description | count
-------------+-------
No of rows | 20
(1 row)
-- Wrapping Outer Query
dbadmin=> select count(*) from (select 'No of rows' as description, count(*) as cnt from tables) as sub;
count
-------
20
(1 row)
-- I was expecting The output as 1 but I get 20 since the inner query count(*) value is 20
But when I change my query in the below format, I get the expected output. What is the reason , Could anyone explain please.
dbadmin=> select count(*) from (
dbadmin(> Select Description, cnt from (
dbadmin(> select 'No of Rows' as description , Count(*) as cnt from tables) as sub1
dbadmin(> group by Description, cnt) as sub2;
count
-------
1
(1 row)
Thanks in Advance.
Select count(*) in Sub query
Moderator: NorbertKrupa
Re: Select count(*) in Sub query
Interesting. Looks like a bug. It was as expected without the ‘No of rows’ tacked on.
explain select count(*) from (select 'No of rows' as description, count(*) as cnt from tables) as sub;
Access Path:
+-GROUPBY NOTHING [Cost: 609, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Aggregates: count(*)
| Execute on: Query Initiator
| Execute on: Query Initiator
| +---> STORAGE ACCESS for t [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 3)
| | Projection: v_internal.vs_tables_view_p
| | Materialize: t.table_schema_id
| | Execute on: Query Initiator
explain select count(*) from (select count(*) as cnt from tables) as sub;
Access Path:
+-GROUPBY NOTHING [Cost: 608, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Aggregates: count(*)
| Execute on: Query Initiator
| Execute on: Query Initiator
| +---> GROUPBY NOTHING [Cost: 607, Rows: 1 (NO STATISTICS)] (PATH ID: 3)
| | Aggregates: count(*)
| | Execute on: Query Initiator
| | +---> STORAGE ACCESS for t [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 4)
| | | Projection: v_internal.vs_tables_view_p
| | | Materialize: t.table_schema_id
| | | Execute on: Query Initiator
explain select count(*) from (select 'No of rows' as description, count(*) as cnt from tables) as sub;
Access Path:
+-GROUPBY NOTHING [Cost: 609, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Aggregates: count(*)
| Execute on: Query Initiator
| Execute on: Query Initiator
| +---> STORAGE ACCESS for t [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 3)
| | Projection: v_internal.vs_tables_view_p
| | Materialize: t.table_schema_id
| | Execute on: Query Initiator
explain select count(*) from (select count(*) as cnt from tables) as sub;
Access Path:
+-GROUPBY NOTHING [Cost: 608, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Aggregates: count(*)
| Execute on: Query Initiator
| Execute on: Query Initiator
| +---> GROUPBY NOTHING [Cost: 607, Rows: 1 (NO STATISTICS)] (PATH ID: 3)
| | Aggregates: count(*)
| | Execute on: Query Initiator
| | +---> STORAGE ACCESS for t [Cost: 604, Rows: 10K (NO STATISTICS)] (PATH ID: 4)
| | | Projection: v_internal.vs_tables_view_p
| | | Materialize: t.table_schema_id
| | | Execute on: Query Initiator
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Select count(*) in Sub query
That is interesting... I agree with Sharon in that it is a bug.
If you add a GROUP BY on the description column, you get a row count of one
If you add a GROUP BY on the description column, you get a row count of one
Code: Select all
dbadmin=> select count(*) from (select 'No of rows' as description, count(*) as cnt from tables group by description) as sub;
count
-------
1
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.