roosterup
asked on
Total the number of unique customer numbers in a table with an sql statement
I have a table with a customer number field. The customer number can be in the table multiple times. I need to total of the number of unique customer numbers in the file. In other words, how many customers do I have?
How can I do this in sql?
How can I do this in sql?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jim Horn
Thanks for the grade. I have an article out there on SQL Server GROUP BY Solutions, and just noticed that COUNT(DISTINCT) is not on it, so I'll have to add it. Thanks.
The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:
eg. : SELECT COUNT(DISTINCT column_name) FROM table_name;
Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.
Example:
The following SQL statement counts the number of unique customers in the "Orders" table:
SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM Orders;
When count(distinct) is not supported (as in access), you can split the count and distinct in a sub-query:
Example:
SELECT count(*)
FROM
(SELECT DISTINCT CustNbr FROM CustTable1)
eg. : SELECT COUNT(DISTINCT column_name) FROM table_name;
Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.
Example:
The following SQL statement counts the number of unique customers in the "Orders" table:
SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM Orders;
When count(distinct) is not supported (as in access), you can split the count and distinct in a sub-query:
Example:
SELECT count(*)
FROM
(SELECT DISTINCT CustNbr FROM CustTable1)
ok, thanks