Using the COUNT function in SQL

Question

First and Foremost, this is part of an assignment.

I am trying to use the COUNT function as part of a query in relation to the Northwind database. The query should return the CustomerID, CompanyName, and the number of of orders placed for each respective customer.

Of course the first two parts are easy, but I can't get the COUNT function to work properly. The query I have so far is:

SELECT DISTINCT Customers.CustomerID, Customers.CompanyName, COUNT(Customers.CustomerID)
FROM Orders, Customers
WHERE Customers.CustomerID = Orders.CustomerID;

What would be the correct syntax to use COUNT in that fashion? It would look like:

CompanyID | CompanyName | # of orders
    1     | Company A   | 4
    2     | Company B   | 3
    3     | Company C   | 5

All examples thus far have been using the COUNT function by itself, and not part of a more complex query.

Hint: You have to use GROUP BY and LEFT JOIN (so that customers with no order show up) — Lucas Trzesniewski
– Lucas Trzesniewski, Commented Jan 27, 2015 at 23:39
This is an assginment. Perhaps you can try some more before coming here — Santhosh
– Santhosh, Commented Jan 27, 2015 at 23:41

Mureinik · Accepted Answer · 2015-01-27 23:42:08Z

2

You need a group by clause, which will allow you to split your result in to groups, and perform the aggregate function (count, in this case), per group:

SELECT   Customers.CustomerID, Customers.CompanyName, COUNT(*)
FROM     Orders, Customers
WHERE    Customers.CustomerID = Orders.CustomerID;
GROUP BY Customers.CustomerID, Customers.CompanyName

Note: Although this is not part of the question, it's recommended to use explicit joins instead of the deprecated implicit join syntax you're using. In this case, the query would look like:

SELECT   Customers.CustomerID, Customers.CompanyName, COUNT(*)
FROM     Orders
JOIN     Customers ON Customers.CustomerID = Orders.CustomerID;
GROUP BY Customers.CustomerID, Customers.CompanyName

answered Jan 27, 2015 at 23:42

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt Harper · Accepted Answer · 2015-01-28 00:12:24Z

1

The below query should work.

    SELECT Customers.CustomerID, Customers.CompanyName, COUNT(*)
    FROM Orders, Customers
    WHERE Customers.CustomerID = Orders.CustomerID group by Orders.CustomerID

edited Jan 28, 2015 at 0:12

Matt Harper

1228 bronze badges

answered Jan 27, 2015 at 23:43

user2644239

462 bronze badges

Comments

Matt Harper · Accepted Answer · 2015-01-27 23:41:21Z

0

When using aggregate functions like COUNT(), all columns have to be part of aggregate functions. The GROUP BY statement should solve your problem. http://www.w3schools.com/sql/sql_groupby.asp

answered Jan 27, 2015 at 23:41

Matt Harper

1228 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2015-01-27 23:41:41Z

0

First, if you are learning SQL, you should learn proper explicit join syntax. Simple rule: never use a comma in the from clause.

Second, your query should use group by instead of distinct. In fact, it is more important to learn group by than to learn distinct, because you can generally write select distinct using group by.

So, where you are heading is:

SELECT c.CustomerID, c.CompanyName, COUNT(c.CustomerID)
FROM Orders o JOIN
     Customers c
     ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CompanyName;

answered Jan 27, 2015 at 23:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Jean-François Savard Over a year ago

is there a reason why you don't use the using keyword or just a matter of preference, I'm curious ?

Gordon Linoff Over a year ago

@Jean-FrançoisSavard . . . using is not supported by all databases.

Jean-François Savard · Accepted Answer · 2015-01-28 01:11:12Z

0

You are missing the group by clause. Your query should look like this

select CustomerID, Customers.CompanyName, count(CustomerID)
from Orders JOIN Customers using(CustomerID)
group by CustomerID;

Notice that I used the Join syntax which is more concise when joining tables.

edited Jan 28, 2015 at 1:11

answered Jan 27, 2015 at 23:39

Jean-François Savard

21k7 gold badges54 silver badges78 bronze badges

Collectives™ on Stack Overflow

Using the COUNT function in SQL

5 Answers 5

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related