Getting unique ids affected by a group by clause

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stephane - 31 Jul 2008 16:40 GMT

Hi,

Let's say I have a customers table and an orders table. In the orders table,
I have the customer name.

So if I want to select the orders grouped by customer name where the
customer has more than 3 orders, it's easy:

select avg(amount), customerName from tbl_orders group by customerName
having count(1) > 3

Now for the complexe part, let's say I also want those orders, but over a
period of time for each day. I'd do:

select avg(amount), date from tbl_orders where date between '2008-06-01' and
'2008-07-01'
and customerName in
(select customerName from tbl_orders where date between '2008-06-01' and
'2008-07-01' group by customerName having count(1) > 3)
group by date

I'd like to know every order Ids affected by this query so I could make a
join on the orderId instead of the customerName. There's a clustered index on
the orderId, no index on the customerName. I don't want to create an index on
customerName because there are other columns for which I have the same
problem.

Is there a way to get all there unique ids affected by a group by clause? I
think this might accelerate some of my queries.

Thanks for any help

Stephane

Reply to this Message

--CELKO-- - 31 Jul 2008 17:16 GMT

>> Let's say I have a Customers table and an Orders table. In the Orders table, I have the customer name. <<

That is a design error. A customer's name is an attribute of a
Customer, not an Order. An order is related to a customer, probably
thru a customer id or account number of some kind. You also failed to
post any DDL or sample data. You used a reserved word, "date" for a
column name -- is it the order_date or another attribute of the
customer? Likewise "amount" is too vague to be useful.

>> I'd like to know every order_id affected by this query [queries do not affect data] so I could make a join on the order_id instead of the customer_name. <<

This should give you the orders placed in that date range by customers
who had 3 or more orders

SELECT order_id
FROM (SELECT customer_id, order_id,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY
order_id)
FROM Orders
WHERE order_date BETWEEN '2008-06-01' AND '2008-07-01')
AS X (customer_id, order_id, order_seq)
WHERE order_seq >= 3;

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 31 Jul 2008 17:23 GMT

> Customer, not an Order. An order is related to a customer, probably
> thru a customer id or account number of some kind.

Ooh, I like where this is going. Where does the magical "customer id" or
"account number" come from?

A

Reply to this Message