query help - Page 2

r937 · #16 (**permalink**) 12-04-08, 15:39

the SUM expression counts all transactions that were over average

the COUNT counts all transactions

when these are equal, this means there were no transactions less than average

per customer

JarlH · #17 (**permalink**) 12-05-08, 05:45

Quote:

Originally Posted by stolze

@JarHL: I don't see how you could possibly interpret the explanations you cited as an argument against subselects in a WHERE clause or SELECT list.

A scalar subselect is an expression. Based on that, you can use subselects in both, a WHERE clause and the SELECT list. Even more so, there are predicates like EXISTS, IN, =ANY, =ALL, etc. that operate on whole sets, i.e. subselects.

Your absolutely right. (I suppose I'd read more carefully before answering.)

khdani · #18 (**permalink**) 12-05-08, 12:25

I got it now,
Thank you

Peter.Vanroose · #19 (**permalink**) 12-05-08, 16:48

Quote:

Originally Posted by r937

SELECT custid
FROM ord
GROUP
BY custid
HAVING COUNT(*) =
SUM(CASE WHEN total > (SELECT AVG(total) FROM ord)
THEN 1 ELSE NULL END)

Here's yet an other query which returns the required result:

Code:

SELECT custid
  FROM ord, (select avg(total) as average from ord) dv
GROUP BY custid, average
HAVING MIN(total) > average

It's closer to the original query of khdani (with the join to a 1x1 table).
The "MIN(total) >" clearly expresses that all totals within the same group (of orders for the same customer) need to be larger than something.

Pat Phelan · #20 (**permalink**) 12-05-08, 17:59

While Peter's solution is elegant, I think that it would fail if there are any NULL values of total, which I would expect to exclude a customer from selection. The query that r937 provided should handle NULL values for total as I think they ought to be handled. If the total column is declared NOT NULL, then obviously this point is moot.

-PatP

Peter.Vanroose · #21 (**permalink**) 12-05-08, 18:52

Quote:

Originally Posted by Pat Phelan

While Peter's solution is elegant, I think that it would fail if there are any NULL values of total, which I would expect to exclude a customer from selection.

Yes, you're right.
If I'm not mistaken, that problem could be fixed by replacing MIN(total) by MIN(COALESCE(total,average+1))
Less elegant, of course ...

Internet Services

Web Development

Digital Marketing

Consumer Tech