Add a count to an SQL select statement

earwig75
earwig75 used Ask the Experts™
on
I would like to make a select statement, using a select distinct, and include a count in each record returned.

For example, for the dataset below, I would like the record to return only 1 instance of the "order number" and a count of how many different ones there are.

Can someone help with this? Thank you.

I would like the return to show this, from the dataset below it.

Query return:
Order Number | Count
1234567                 3
7896548                 1

Data set:
ID | Order Number
1     1234567                    
2     1234567
3     1234567
4     7896548
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
System Architect, CF programmer
Commented:
try this.

Select orderNumber,Count(*) as count
from table
GRoup BY orderNumber
Jerry_Justice

Commented:
Eric has the right answer..

This one is slightly different.. I always use the Field Name in the count function instead of *.  Not sure if * will work on all database systems.

Select orderNumber,Count(orderNumber) as count
from table
GRoup BY orderNumber
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
COUNT(*) works in every SQL compliant rdbms I have ever used (quite a few).

The asterisk in that position is NOT like saying "select *"
i.e. for COUNT() no more data is accessed if you use COUNT(*)

If you specify a column name instead of the asterisk be wary of NULLs in that column, because COUNT() will ONLY increment for a non-null value.

e.g.

Table1
id   code
1    'a'
2    NULL
3    'b'

select count(*) from table1 ==> 3

select count(id) from table1 ==> 3

select count(code) from table1 ==> 2

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial