0

I'm using the northwind.sql.

This is the schema diagram for the northwind.sql.

https://brucebauer.info/assets/ITEC3610/Northwind/Northwind-Sample-Database-Diagram.pdf

Objective:

Write a query to get the name of customers and the numbers of orders placed by each.

Answer:

SELECT 
    c.CompanyName,count(o.orderid)  FROM
    customers c
        INNER JOIN
    orders o ON c.customerid = o.customerid
GROUP BY c.CompanyName;

This query works And generates the following output.

https://imgur.com/a/KVK6Ase

If I change GROUP BY to o.customerid, it still works! I don't know why?

SELECT 
    c.CompanyName,count(o.orderId)  FROM
    customers c
        INNER JOIN
    orders o ON c.customerid = o.customerid
GROUP BY o.customerid;

If I change count(o.orderid) to count(1) or count(*) , it still works and generates same output albeit with different column header.

Why do I need to write the count() statement at all if it's not meaningful?

AFAIK, COUNT(column); example say COUNT(o.orderid) counts total numbers of orderid barring NULL in the table.

count(*) returns the number of rows as selected by SELECT statement.

And in the same way COUNT(1) should just count 1 no matter how many rows are there.

Edit: I was wrong about COUNT(1), the first google search says this:

COUNT(1) The COUNT(1) function replaces all records from the query result set with value 1. If you have NULL values, it is also replaced by 1. Therefore, COUNT(1) also returns the total number of records (including NULLs) in the table.

5
  • 1
    If I change GROUP BY to o.customerid, it still works! I don't know why? Because of current SQL Mode (ONLY_FULL_GROUP_BY is not enabled).
    – Akina
    Commented Jun 8, 2023 at 11:52
  • COUNT(1) and COUNT(*) produces the same output. Always.
    – Akina
    Commented Jun 8, 2023 at 11:52
  • Do yourself a favour and enable ONLY_FULL_GROUP_BY it saves a lot of heartache Commented Jun 8, 2023 at 13:22
  • I don't know if that answers my question but I said yes.
    – achhainsan
    Commented Jun 8, 2023 at 14:04
  • ONLY_FULL_GROUP_BY is enabled. Check my new post. @Charlieface
    – achhainsan
    Commented Jun 9, 2023 at 5:56

0

Browse other questions tagged or ask your own question.