How to Count Occurrences in a Column while including another column with unique values with SQL?

Question

I have a table of customers:

id	order
0	25
1	25
2	23
3	24
4	25

I want to query for all customers, and an additional column that counts how many customers are of the same order number:

id	order	count
0	25	3
1	25	3
2	21	1
3	25	3
4	22	1

I have tried this syntax

SELECT A.id, A.0rder, B.cnt
  FROM Customers  A
  INNER JOIN (
    SELECT Order, count(Order) as cnt
      FROM Customers 
      GROUP BY Order
  ) B ON A.Order = B.Order

and ran into this error. 'Error saving the Query field.Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax.'

Answer 1

The issue with your query is that your column named order is also a keyword eg ORDER BY. You may resolve this by using a table alias as shown below:

SELECT 
    A.id, A.order, 
    B.cnt 
FROM 
    Customers A 
INNER JOIN (
  SELECT b.Order, count(b.Order) as cnt 
  FROM Customers b GROUP BY b.Order
) B ON A.Order = B.Order;

id	order	cnt
0	25	3
1	25	3
2	23	1
3	24	1
4	25	3

---

View on DB Fiddle

You may also use a window function COUNT with a partition on the order column or use a correlated query to achieve this. Both approaches have been included below.

Query #1 - Window Functions

SELECT
    t1.*,
    COUNT(1) OVER (PARTITION BY t1.order) as count
FROM
    my_table t1
ORDER BY 
    t1.id;

id	order	count
0	25	3
1	25	3
2	23	1
3	24	1
4	25	3

---

Query #2

SELECT
    t1.*,
    (
        SELECT COUNT(1) FROM my_table t2 WHERE t2.order=t1.order
    ) as count
FROM
    my_table t1
ORDER BY 
    t1.id;

id	order	count
0	25	3
1	25	3
2	23	1
3	24	1
4	25	3

---

View Demo on DB Fiddle

Let me know if this works for you.