Postgres Pro
Downloads
Home   >   mailing lists

Thread: select count(*)

select count(*)

From
Rajesh Kumar Mallah
Date:
Dear List ,

if we simply do select count(*) and not specify any table then it gives 1 eg:

bric=# SELECT count(*)  from job ;
 count
-------
  2380
(1 row)

bric=# SELECT count(*)  job ;
 job
-----
   1
(1 row)



bric=# SELECT count(*)  ;
 count
-------
     1
(1 row)



bric=# SELECT count(*)  job_non_exist ;
 job_non_exist
---------------
             1
(1 row)

bric=# SELECT count(*)  jo1b ;
 jo1b
------
    1
(1 row)

bric=# SELECT count(*)  none ;
ERROR:  syntax error at or near "none"
LINE 1: SELECT count(*)  none ;


I fail to see any  progression ?


regds
mallah.

Re: select count(*)

From
Bill Moran
Date:
In response to Rajesh Kumar Mallah <mallah.rajesh@gmail.com>:

> Dear List ,
>
> if we simply do select count(*) and not specify any table then it gives 1
> eg:
>
> bric=# SELECT count(*)  from job ;
>  count
> -------
>   2380
> (1 row)
>
> bric=# SELECT count(*)  job ;
>  job
> -----
>    1
> (1 row)
>
>
>
> bric=# SELECT count(*)  ;
>  count
> -------
>      1
> (1 row)
>
>
>
> bric=# SELECT count(*)  job_non_exist ;
>  job_non_exist
> ---------------
>              1
> (1 row)
>
> bric=# SELECT count(*)  jo1b ;
>  jo1b
> ------
>     1
> (1 row)
>
> bric=# SELECT count(*)  none ;
> ERROR:  syntax error at or near "none"
> LINE 1: SELECT count(*)  none ;
>
>
> I fail to see any  progression ?

When you don't specify a FROM clause, you get 1 because it's
returning 1 row.  No matter what you alias the result to, it's not going
to change the result, unless of course you try to alias it to an SQL
reserved word, such as "none", without quoting it.  Of course, if you
include the optional AS, it probably makes more sense what's going on:

SELECT count(*) AS jolb;
SELECT count(*) AS none;
SELECT count(*) AS "none";

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Re: select count(*)

From
Rajesh Kumar Mallah
Date:
i "discovered" it as a result of typo :)

we usually select expressions without tables
eg select 1+2 ; etc and the results are as expected,
somehow i failed to stretch the analogy to count(*)
which is mostly used over tables or table expression.

thanks anyways.

regds
mallah.


On Wed, Mar 9, 2011 at 11:20 PM, Bill Moran <wmoran@potentialtech.com> wrote:
In response to Rajesh Kumar Mallah <mallah.rajesh@gmail.com>:

> Dear List ,
>
> if we simply do select count(*) and not specify any table then it gives 1
> eg:
>
> bric=# SELECT count(*)  from job ;
>  count
> -------
>   2380
> (1 row)
>
> bric=# SELECT count(*)  job ;
>  job
> -----
>    1
> (1 row)
>
>
>
> bric=# SELECT count(*)  ;
>  count
> -------
>      1
> (1 row)
>
>
>
> bric=# SELECT count(*)  job_non_exist ;
>  job_non_exist
> ---------------
>              1
> (1 row)
>
> bric=# SELECT count(*)  jo1b ;
>  jo1b
> ------
>     1
> (1 row)
>
> bric=# SELECT count(*)  none ;
> ERROR:  syntax error at or near "none"
> LINE 1: SELECT count(*)  none ;
>
>
> I fail to see any  progression ?

When you don't specify a FROM clause, you get 1 because it's
returning 1 row.  No matter what you alias the result to, it's not going
to change the result, unless of course you try to alias it to an SQL
reserved word, such as "none", without quoting it.  Of course, if you
include the optional AS, it probably makes more sense what's going on:

SELECT count(*) AS jolb;
SELECT count(*) AS none;
SELECT count(*) AS "none";

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/