Counting based on a field combination

[calan]

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

[strider5]

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

[David Portas]

Please give proper DDL with your posts, including the definition of
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
with your data.

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
--