Where vs. Group By

[jmeckley]

up until now I have always used the where clause for filtering. just for kicks I experimented with a query today

--query A.
select	b.broker_id, b.broker, sum(c.commission_amount)
from	broker b
	inner join order_commission_status s on b.broker_id = s.broker_id
	inner join order_detail_commission c on b.broker_id = c.broker_id
where	(s.status = 1 or s.status = 2)
group by b.broker_id, b.broker
having	sum(c.commission_amount) > 0

--query B.
select	b.broker_id, b.broker, sum(c.commission_amount)
from	broker b
	inner join order_commission_status s on b.broker_id = s.broker_id
	inner join order_detail_commission c on b.broker_id = c.broker_id
group by b.broker_id, b.broker, s.status
having	sum(c.commission_amount) > 0
 and	(s.status = 1 or s.status = 2)

the extended execution plan
query A 50.08%
query B 49.92%

i realize the difference is small, but I'm wondering what is the general practice/guidelines for using Group By vs. Where clause

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[gmmastros]

Just for fun, try Query C.

[COLOR=green]--query C.
[/color][COLOR=blue]select[/color]  b.broker_id, b.broker, sum(c.commission_amount)
[COLOR=blue]from[/color]    broker b
        [COLOR=blue]inner[/color] [COLOR=blue]join[/color] order_commission_status s 
          [COLOR=blue]on[/color]  b.broker_id = s.broker_id
          And (s.status = 1 or s.status = 2)
        [COLOR=blue]inner[/color] [COLOR=blue]join[/color] order_detail_commission c [COLOR=blue]on[/color] b.broker_id = c.broker_id
[COLOR=blue]group[/color] [COLOR=blue]by[/color] b.broker_id, b.broker
[COLOR=blue]having[/color]  sum(c.commission_amount) > 0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

Do you mean having vs where clause?

Your two queries aren't really the same (because you're grouping by status in the second one) so I am not sure what you are getting at.

As a rule, I try to use HAVING only when I need an aggregate. So I would use HAVING for this:

sum(c.commission_amount) > 0

And WHERE for this:

and (s.status = 1 or s.status = 2)

I would try making the two group by lines the same, and then take a look at the query plans.

Hope this helps,

Alex





Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

Ol' George and his query C's.

<.

 

[jmeckley]

Your two queries aren't really the same (because you're grouping by status in the second one) so I am not sure what you are getting at.

good point. the 1st query doesn't group by status, because it isn't needed (at least SQL doesn't error, and the results are the same).

adding query C to the mix produced this result.
query A 33.37%
query B 33.26%
query C 33.37%

if i change the querie to group by the same fields [tt]b.broker_id, b.broker, s.status[/tt]. the query plans are equal 33.33%.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[gmmastros]

How long (approximately) do these queries take to run?
Do you have an index on the status column?
Do you have indexes on the broker column?
How many records (approximately) are in each table?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom