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,
- SQL MIN Function | MIN() function in SQL
- SQL COUNT() Function ,Usage of COUNT(*) and COUNT(1)
- SQL order by ,using ‘ORDER BY’ clause in SQL
- Oracle Max(Date)? | sql max date
- Oracle Trim Function | PL/SQL TRIM Function.
- Oracle Decode Function | Use PLSQL DECODE function to handle NULL and default values
- SQL: MAX Function | SQL Max examples
- SQL GROUP BY Statement | SQL GROUP BY Clause examples
- NVL | Oracle/PLSQL: NVL Function
Technorati Tags:
SQL COUNT() Function, COUNT(*), COUNT(1), SQL COUNT
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.
Nice web. Maybe you should try to monetize it with
http://tiny.cc/05mrc
They do have 40 days cookie duration and they pay 10-15% commisions. They’ll match ads based on search query of your visitors and display relevant ads on you blog.