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
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
Last Comment
PortletPaul
ASKER CERTIFIED SOLUTION
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.
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
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
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(orderNum
from table
GRoup BY orderNumber