Link to home
Start Free TrialLog in
Avatar of earwig75
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
ASKER CERTIFIED SOLUTION
Avatar of erikTsomik
erikTsomik
Flag of United States of America image

Link to home
membership
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
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.

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