469,923 Members | 1,777 Online

# Counting based on a field combination

Having a brainfart....

I need a query that returns a record count, based on two distinct fields.

For example:

Order Revision Customer
001 1 Bob
001 2 Bob
002 1 John
003 1 John
004 1 John
005 1 Bob
006 1 Bob
006 2 Bob
The query on the above data should return a count of orders, regardless of
the revision numbers (each order number should only be counted once).

So WHERE Customer = 'Bob', it should return OrderCount = 3

TIA!

Calan
Jul 23 '05 #1
2 1551
The query on the above data should return a count of orders, regardless of the revision numbers (each order number should only be counted once).

So WHERE Customer = 'Bob', it should return OrderCount = 3

TIA!

Calan

--something like this?

create table #foo (
Ord int,
Revision int,
Customer varchar(10)
primary key (ord,revision)
)
GO

insert into #foo( Ord, Revision, Customer )Values(001, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(001, 2, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(002, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(003, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(004, 1, 'John' )
insert into #foo( Ord, Revision, Customer )Values(005, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(006, 1, 'Bob' )
insert into #foo( Ord, Revision, Customer )Values(006, 2, 'Bob' )

select customer,count( distinct ord) as 'orders'
from #foo
group by customer

drop table #foo
go

--Strider

Jul 23 '05 #2
keys and constraints. They can make a big difference to the answer.
Post sample data as INSERT statements so that we can test out solutions

Assuming the key in this case is (order,revision):

SELECT customer, count(*)
FROM Orders
GROUP BY customer

If I'm wrong about the key, try:

SELECT customer, count(DISTINCT order)
FROM Orders
GROUP BY customer

(both untested)

The COUNT(DISTINCT...) version typically runs significantly slower.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #3

### This discussion thread is closed

Replies have been disabled for this discussion.