Link to home
Start Free TrialLog in
Avatar of earwig75

asked on

Add a count to an SQL select statement

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
Avatar of erikTsomik
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jerry_Justice

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
Avatar of PortletPaul
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.


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