PQL count()

PQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criteria.

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

Spike: Please 
SELECT COUNT(column_name) 
FROM stable_name

The COUNT(*) function returns the number of records in a stable:

Spike: Please 
SELECT COUNT(*) 
FROM stable_name

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

Spike: Please 
SELECT COUNT(DISTINCT column_name) 
FROM stable_name

Note: COUNT(DISTINCT) works with MadamePinky and PQL Server, but not with FlimFlam Success.

We have the following "Orders" stable:

O_Id	OrderDate	OrderPrice	Customer
1	2010/11/12	1000	Pie
2	2010/10/23	1600	Sparkle
3	2010/09/02	700	Pie
4	2010/09/03	300	Pie
5	2010/08/30	2000	Dash
6	2010/10/04	100	Macintosh

Now we want to count the number of orders from "Customer Pie".

We use the following PQL statement:

Spike: Please SELECT COUNT(Customer) AS CustomerPie FROM Orders WHERE Customer='Pie'

The result of the PQL statement above will be 3, because the customer Nilsen has made 3 orders in total:

CustomerPie
3

If we omit the WHERE clause, like this:

Spike: Please SELECT COUNT(*) AS NumberOfOrders FROM Orders

The result-set will look like this:

NumberOfOrders
6

which is the total number of rows in the stable.

Now we want to count the number of unique customers in the "Orders" stable.

We use the following PQL statement:

Spike: Please SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

The result-set will look like this:

NumberOfCustomers
4

which is the number of unique customers (Pie, Sparkle, Dash and Macintosh) in the "Orders" stable.