Select Count(*) ;

select count(*) from select

You're missing a FROM and you need to give the subquery an alias.

SELECT COUNT(*) FROM 
(
  SELECT DISTINCT a.my_id, a.last_name, a.first_name, b.temp_val
   FROM dbo.Table_A AS a 
   INNER JOIN dbo.Table_B AS b 
   ON a.a_id = b.a_id
) AS subquery;

Sql: Select count(*) from (select ...)

SELECT COUNT(*)
FROM 
(
select p.UserName, p.FirstName + ' ' + p.LastName as [FullName]
 ,count(b.billid) as [Count], sum(b.PercentRials) as [Sum] from Bills b
inner join UserProfiles p on b.PayerUserName=p.UserName
 where b.Successful=1
 group by p.UserName, p.FirstName + ' ' + p.LastName   --<-- Removed the extra comma here
) A  --<-- Use an Alias here

As I expected from your shown attempt you were missing an Alias

select count(*) 
from (select ...) Q  --<-- This sub-query in From clause needs an Alias 

Edit

If you only need to know the rows returned by this query and you are executing this query anyway somwhere in your code you could simply make use of @@ROWCOUNT function. Something like....

SELECT ......     --<-- Your Query

SELECT @@ROWCOUNT    --<-- This will return the number of rows returned 
                       -- by the previous query 

How to re-name and reference COUNT(*) in a SELECT statement?

You can alias a column by simply putting a name after it, optionally with the keyword AS in between. It's essentially the same as you already do with the tables.

SELECT school_name,
       (SELECT count(*)
               FROM liason_to l
               WHERE l.school_name = s.school_name) AS numliasons
       FROM school s;

or simply

SELECT school_name,
       (SELECT count(*)
               FROM liason_to l
               WHERE l.school_name = s.school_name) numliasons
       FROM school s;

But you cannot use aliases in the WHERE clause (aliasing is happening after the records have been selected by the criteria in the WHERE clause). You have to repeat the expession.

SELECT school_name,
       (SELECT count(*)
               FROM liason_to l
               WHERE l.school_name = s.school_name) numliasons
       FROM school s
       WHERE (SELECT count(*)
                     FROM liason_to l
                     WHERE l.school_name = s.school_name) > 0;

Optimizing SELECT count(*) on large table

The workers planned is 4 but launched 0, is that normal?

It can happen when too many concurrent transactions compete for a limited number of allowed parallel workers. The manual:

The number of background workers that the planner will consider using
is limited to at most max_parallel_workers_per_gather. The
total number of background workers that can exist at any one time is
limited by both max_worker_processes and
max_parallel_workers. Therefore, it is possible for a
parallel query to run with fewer workers than planned, or even with no
workers at all. The optimal plan may depend on the number of workers
that are available, so this can result in poor query performance. If
this occurrence is frequent, consider increasing
max_worker_processes and max_parallel_workers so that more workers
can be run simultaneously or alternatively reducing
max_parallel_workers_per_gather so that the planner requests fewer
workers.

You can also optimize overall performance to free up resources, or get better hardware (in addition to ramping up max_parallel_workers).

What's also troubling:

Heap Fetches: 46261956

For 90993600 rows. That's way too many for comfort. An index-only scan is not supposed to do that many heap fetches.

Both of these symptoms would indicate massive concurrent write access (or long-running transactions hogging resources and keeping autovacuum from doing its job). Look into that, and/or tune per-table autovacuum settings for table product to be more aggressive, so that columns statistics are more valid and the visibility map can keep up. See:

• Aggressive Autovacuum on PostgreSQL

Also, with halfway valid table statistics, a (blazingly fast!) estimate might be good enough? See:

• Fast way to discover the row count of a table in PostgreSQL

SELECT COUNT(1) FROM table in dynamic query

Bind variables are for variables, not identifiers. The table name (and other identifiers) need to be known when the dynamic statement is parsed; variable values are then applied when it is executed. At the moment when the statement is parsed the table name is interpreted literally as :tab_name, which is an invalid name.

You need to concatenate the name:

    empty_sql := 'SELECT COUNT(1) FROM ' || table_name;
    EXECUTE IMMEDIATE empty_sql INTO row_count;

You might want to validate the table name first though.

Order by select count(*) and LIMIT is very slow

SELECT t.*, COUNT(*) AS count
FROM tags AS t
LEFT OUTER JOIN post_tag AS pt ON t.id = pt.tag_id
GROUP BY t.id
ORDER BY count ASC LIMIT 15 OFFSET 0;

You should make sure post_tag has an index starting with the tag_id column. You didn't include your table definition in your question, so I must assume the index is there. If the primary key starts with tag_id, that's okay too.

You don't need to join to posts, if I can assume that a row exists in post_tag means it must reference an existing row in posts. You can get the information you need only by joining to post_tag.

---

Related Topics