# Not getting the concept of COUNT with GROUP BY?

This is not my job, neither my homework. I'm just self-learnin from Prof. Donatus course on udemy and these are the problems he used in his course.

Objective 1: Your logistics manager wants to know the number of times each ship region appears in the company database. Use SQL query to get the details.

Objective 2: Using the shipregion column, count how many times each value has appeared in the column.

The database: Northwind

The table: orders

Solution of 1:

``````SELECT ShipRegion,
COUNT(ShipRegion)
FROM
orders
GROUP BY ShipRegion;
``````

Solution of 2:

``````SELECT ShipRegion,
COUNT(*)
FROM
orders
GROUP BY ShipRegion;
``````

I understand the first solution but not the second solution.

My confusion: How does doing COUNT(*) makes sure to count all shipregions only? Is it because group by shipregion is used?

If that's the case, in above solution, we are also usingg COUNT(shipregion). I don't understand. Please help.

• I'm struggling to see the difference between the two objectives. Commented Jun 5, 2023 at 14:49

The COUNT() function is an aggregate function that returns the number of rows in a table. The COUNT() function allows you to count all rows or only rows that match a specified condition.

The COUNT() function has three forms:

1. `COUNT(*)`,
2. `COUNT(expression)`
3. `COUNT(DISTINCT expression)`

`COUNT(*)` function

The `COUNT(*)` function returns the number of rows in a result set returned by a SELECT statement. The `COUNT(*)` returns the number of rows including duplicate, non-NULL and NULL rows.

`COUNT(expression)`

The `COUNT(expression)` returns the number of rows that do not contain NULL values as the result of the expression.

`COUNT(DISTINCT expression)`

The COUNT(DISTINCT expression) returns the number of distinct rows that do not contain NULL values as the result of the expression.

The return type of the `COUNT()` function is BIGINT. The `COUNT()` function returns 0 if there is no matching row found.

Consider a simplified example based on your data

``````CREATE TABLE   `orders` (
`ShipRegion` varchar(6)
) ;

INSERT INTO `orders` VALUES (null),(null),('RJ'),('RJ'),('RJ'),('SP'),('NM'),('NM');
``````

Query1,

``````SELECT ShipRegion,
COUNT(*)
FROM orders
GROUP BY ShipRegion;
``````

Result

``````ShipRegion  COUNT(*)
null       2
RJ         3
SP         1
NM         2
``````

Query2,

``````SELECT ShipRegion,
COUNT( ShipRegion)
FROM  orders
GROUP BY ShipRegion;
``````

Result

``````ShipRegion  COUNT( ShipRegion)
null         0
RJ           3
SP           1
NM           2
``````

Query3,

``````SELECT ShipRegion,
COUNT(distinct ShipRegion)
FROM  orders
GROUP BY ShipRegion;
``````

Result

``````ShipRegion  COUNT(distinct ShipRegion)
null        0
NM          1
RJ          1
SP          1
``````

My confusion: How does doing `COUNT(*)` makes sure to count all shipregions only? Is it because group by shipregion is used?

Yes, The `COUNT(*)` function is often used with a `GROUP BY` clause to return the number of elements in each group.

In the first Objective I think you need to apply where shipregion is not null, because null is not equal to anything

``````SELECT ShipRegion,
COUNT(*)
FROM orders
WHERE ShipRegion is not null
GROUP BY ShipRegion;
``````

See demo here

• `SELECT c.CompanyName, COUNT(o.orderid) FROM customers c INNER JOIN orders o ON c.customerid = o.customerid GROUP BY c.CompanyName;` Here we're doing COUNT(expression) but we're not grouping by that expression and it still works, why? How? It's still the northwind database. brucebauer.info/assets/ITEC3610/Northwind/… Commented Jun 7, 2023 at 8:33
• @achhainsan in simple words in your first query you are counting how many values are per each CompanyName including non null values. If instead of `COUNT(o.orderid)` you use `COUNT(c.CompanyName)` the only diffrence would be that will be counted only non null values. count(1) still counts the all values including nulls . I hope it's more clear. Commented Jun 7, 2023 at 14:20