Select Count(*) from Select

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;

Select Count(*) vs Select Count(id) vs select count(1). Are these indeed equivalent?

count(*)  --counts all values including nulls

count(id)-- counts this column value by excluding nulls

count(1) is same as count(*)

If we add a where clause: where msg_type = X; if msg_type has a non_clustered index, would select count(msg_type) from table_name where msg_type = X be the preferred option for counting?

As i mentioned in my previous answer ,SQL server is a cost based optimizer and the plan choosen depends on many factors .sql tries to retrieve cheapest plan in minimum time possible..

now when you issue,count(msg_type),SQL may choose this index if this is cheaper or scan another one as long as it gives right results(no nulls in output)..

I always tend to use count(*) ,unless i want to exclude nulls

SELECT with a COUNT of another SELECT

MySQL VERSION

just join a count that is joined by id.

SELECT t.*, COALESCE(t1.status_3_count, 0) as status_3_count
FROM yourtable t
LEFT JOIN 
(   SELECT id, SUM(status=3) as status_3_count
    FROM yourtable
    GROUP BY id
) t1 ON t1.id = t.id
WHERE t.status = 0

note: this is doing the boolean sum (aka count)..
the expression returns either true or false a 1 or a 0. so I sum those up to return the count of status = 3 for each id

SQL SERVER VERSION

SELECT id, SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) as status_3_count
FROM yourtable
GROUP BY id

or just use a WHERE status = 3 and a COUNT(id)

Select count(*) IN ORACLE

You need the execute immediate with into clause.
Here is the adjusted procedure:

DECLARE
tname varchar(255);
sql1 VARCHAR2(2000);
sql2 VARCHAR2(1000);
CNT NUMBER;

CURSOR myCursor IS select table_name from user_tables where table_name like '%VTS';
BEGIN
    OPEN myCursor;
    LOOP
        FETCH myCursor INTO tname;
        EXIT WHEN myCursor%NOTFOUND;
        BEGIN
         sql2 := 'SELECT COUNT(*) FROM ' || tname;
         EXECUTE IMMEDIATE sql2 INTO CNT;       
         DBMS_OUTPUT.put_line( 'Number of rows = : ' || CNT);           
         IF ( CNT ) > 0 THEN 

            SELECT column_name 
              INTO sql1 
              FROM user_tab_cols
             WHERE table_name = tname 
               AND table_name not in (select view_name from user_views) 
               AND data_type = 'VARCHAR2';

           sql1 := 'UPDATE ' || tname || ' SET '|| sql1 || '=''hello''';     
           DBMS_OUTPUT.put_line( 'sql');           
         END IF; 
        END;   
    END LOOP;
    CLOSE myCursor; 
END;

Supplementary remarks:

1. You don't need rownum = 1 when you select just COUNT(*).
2. You need better naming for the variables.

SELECT COUNT query on indexed column

Hash indexes don't store the indexed value in the index, just its 32-bit hash and the ctid (pointer to the table row). That means they can't resolve hash collisions on their own, so it has to go to the table to obtain the value and then recheck it. This can involve a lot or extra IO compared to a btree index, which do store the value and can support index only scans.

---

Related Topics