COUNT() Function in SQL ,Using COUNT(*) and COUNT(1)

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

The COUNT() function returns the number of records in a tables based on sql query.
The COUNT function will only count those records in which the field in the brackets is NOT NULL.

SYNTAX:

SELECT COUNT(expression)
FROM tables
WHERE predicates;

EXAMPLE:
STEP 1:Create the following table,’MYTABLE’ and insert few records.

SQL>  create table MYTABLE (NAME varchar2(50), Age number,id number);
SQL> Table created
SQL> insert into MYTABLE values('BBB',12,1);
SQL> insert into MYTABLE values('DDD',22,2);
SQL> insert into MYTABLE values('CCC',32,3);
SQL> insert into MYTABLE values('AAA',62,4);
SQL> insert into MYTABLE values('CCC',42,5);
SQL>5 rows inserted

STEP 2:Now run the following query.

SQL>  select count(*) from MYTABLE;

COUNT(*)
   5

COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters
If you use COUNT(*) the query will retrieve all fields from the table inorder to calculate count,To avoid this we can use COUNT(1) instead of COUNT(*).It will merely retrieve the numeric value of 1 for each record that meets your criteria.This can be used as a performance tip.

It is possible to use DISTINCT clause within the COUNT function to give exact count omitting duplicates.

SQL> select count(distinct name) names from mytable;
NAMES
  4
SQL>

You can also use GROUP BY function along with COUNT( Check GROUP BY Here).
This is required when you need a combination of coulmns and number of records in a result set.Because you have listed one column in your SELECT statement that is not encapsulated in the COUNT function, you must use a GROUP BY clause.

EXAMPLE: The following query will give you name and number of occurances from MYTABLE.

SQL>  select name ,COUNT( NAME) from MYTABLE GROUP BY name;

NAME                                               COUNT(NAME)
BBB                                                       1
CCC                                                       2
AAA                                                       1
DDD                                                       1

SQL>

EXAMPLE: The following query will return people with age<40

SQL> select name ,count(*)NUMBER_OF_PEOPLE from mytable where age<40;
NAME                                               NUMBER_OF_PEOPLE
BBB                                                 1
CCC                                                 1
DDD                                                 1

SQL>

EXAMPLE: The following query will return people with age>30

SQL> select name ,count(*)NUMBER_OF_PEOPLE from mytable where age >30 group by name;
NAME                                               NUMBER_OF_PEOPLE
CCC                                                           2
AAA                                                           1
SQL>

Related Articles,

Technorati Tags:
, , ,

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

2 thoughts on “COUNT() Function in SQL ,Using COUNT(*) and COUNT(1)

  1. habmannylaL

    Cool blog. Maybe you should try to monetize it with
    http://tiny.cc/05mrc
    They do have 40 days cookie and they pay 10-15% commisions. They’ll match ads based on search query of your visitors and display relevant ads on you blog.
    Now compare this to adsense, ebay, amazon or any other affiliate programs out there.

    Reply

Leave a Reply to habmannylaL Cancel reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page