PostgreSQL - COUNT() Function

The COUNT() function in PostgreSQL is an aggregate function that returns the number of input rows that match a specific condition of a query. It's often used in conjunction with the GROUP BY clause to count the number of rows in each group.

Basic Usage:

Count All Rows:
```
SELECT COUNT(*) FROM table_name;
```
This query will return the total number of rows in table_name.
Count Non-NULL Values:
```
SELECT COUNT(column_name) FROM table_name;
```
This will count all rows where column_name is not NULL.
Count Distinct Values:
```
SELECT COUNT(DISTINCT column_name) FROM table_name;
```
This will return the number of distinct values in column_name.

Examples:

Suppose you have a students table and you want to know how many students there are:
```
SELECT COUNT(*) FROM students;
```
If you want to know how many students have specified their major (assuming the major is stored in a column named major and a NULL value indicates no major specified):
```
SELECT COUNT(major) FROM students;
```
To know how many different majors are represented among the students:
```
SELECT COUNT(DISTINCT major) FROM students;
```
If you want to know how many students are in each major:
```
SELECT major, COUNT(*) FROM students GROUP BY major;
```

Notes:

The COUNT() function will always return a non-negative integer. If there are no matching rows, it will return 0.
When using COUNT() in conjunction with GROUP BY, the function will return the count of rows for each distinct value in the grouped column(s).
As with other aggregate functions, you can use COUNT() with the HAVING clause to filter the results after aggregation. For example, to find majors with more than 10 students:
```
SELECT major, COUNT(*) 
FROM students 
GROUP BY major 
HAVING COUNT(*) > 10;
```

Using the COUNT() function is fundamental in SQL analytics and is often combined with other SQL constructs to derive meaningful insights from the data.

Counting rows in a table with COUNT() in PostgreSQL:
- Description: Simply counts the total number of rows in a table.
- Code:
```
SELECT COUNT(*) FROM your_table;
```
Using COUNT() with GROUP BY in PostgreSQL:
- Description: Counts rows based on groups defined by a specified column.
- Code:
```
SELECT category, COUNT(*) FROM products GROUP BY category;
```
Counting distinct values with COUNT() in PostgreSQL:
- Description: Counts the number of distinct values in a column.
- Code:
```
SELECT COUNT(DISTINCT column_name) FROM your_table;
```
Conditional counting with CASE and COUNT() in PostgreSQL:
- Description: Counts rows based on a specific condition using CASE.
- Code:
```
SELECT COUNT(CASE WHEN condition THEN 1 END) FROM your_table;
```
Handling NULL values with COUNT() in PostgreSQL:
- Description: Counts all rows, including those with NULL values in the specified column.
- Code:
```
SELECT COUNT(column_name) FROM your_table;
```
Counting rows that meet specific conditions in PostgreSQL:
- Description: Counts rows that meet certain conditions in the WHERE clause.
- Code:
```
SELECT COUNT(*) FROM your_table WHERE condition;
```
Using COUNT() in window functions in PostgreSQL:
- Description: Counts rows over a specified window of rows.
- Code:
```
SELECT column, COUNT(*) OVER (PARTITION BY partition_column ORDER BY order_column) FROM your_table;
```