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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
erikTsomik

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy