3

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

enter image description here

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.

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

1 Answer 1

5

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

Read more on Counting Rows

4
  • 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/…
    – achhainsan
    Jun 7, 2023 at 8:33
  • That's even more confusing to me. Can you explain? If result is based on GROUP BY Clause, why do we need COUNT there up?
    – achhainsan
    Jun 7, 2023 at 11:25
  • @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. Jun 7, 2023 at 14:20
  • still not clear. Actually at this point, I don't know what I don't know. I'll form a question later on and ask in reddit as stackexchange doesn't allow duplicates.
    – achhainsan
    Jun 8, 2023 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.