SQL query which includes COUNT(*) in it's SELECT `clause` confuses me-CodePudding

I'm a newbie in SQL, trying to find my way through.

I have the following diagram:

and I'm being requested to

"Produce a list of number of items from each product which was ordered in June 2004. Assume there's a function MONTH() and YEAR()"

The given solution is:

SELECT cat_num, COUNT(*)
FROM ord_rec AS O, include AS I
WHERE O.ord_num = I.ord_num AND
MONTH(O.ord_date) = 6 AND
YEAR(O.ord_date) = 2004
GROUP BY cat_num;

What I'm confused about is the COUNT(*). (specifically the asterisk within).

Does it COUNT all rows that are returned from the given query? So the asterisk refers to all of the returned ROWS? or am I far off?

Is it any different than having:

SELECT cat_num, COUNT(cat_num)

Thanks!

CodePudding user response：

The COUNT(*) function returns the number of rows in a dataset using the SELECT statement. The function counts rows with NULL, duplicate, and non-NULL values.

The COUNT(cat_num) function returns the number of rows that do not contain NULL values.

Consider an example:

Block	Range
A	1-10
A	10-1
B	(NULL)
B	(NULL)
B	(NULL)

For this data,using query:

SELECT 
  COUNT(*),
  COUNT(t.`Block`),
  COUNT(t.`Range`) 
FROM
  `test_table` t

You'll obtain results :

count(*)	count(t.`Block`)	count(t.`Range`)
5	5	2

I hope that clears your confusion.

CodePudding user response：

The COUNT(*) function returns the number of rows in a table in a query. It counts duplicate rows and rows that contain null values.

Overall, you can use * or ALL or DISTINCT or some expression along with COUNT to COUNT the number of rows w.r.t. some condition or all of the rows, depending up on the arguments you are using along with COUNT() function.

Possible parameters for COUNT()

When the * is used for COUNT(), all records ( rows ) are COUNTed if some content NULL but COUNT(column_name) does not COUNT a record if its field is NULL.

Resources here.