SQL where clause effect on table?

Question

I have sql query as

select sourceIP, sum(sourceBytes) 
from flows
group by sourceIP
order by sum(sourceBytes) desc

This brings result (dummy) as:-

sourceIp     SourceBytes
192.168.1.2  100
192.168.1.3  79
192.168.1.4  67
192.168.1.5  4
192.168.1.6  4

Now if I change the query to

select sourceIP, sum(sourceBytes) 
from flows
where sourceBytes > 50
group by sourceIP
order by sum(sourceBytes) desc

The output be

 sourceIp        SourceBytes
 192.168.1.2     150
 192.168.1.3     40

I don't have access to DB right now, I cannot pull /show real table values, but the point here I want to make with the greater then statement the output is changed. I was of the view with second query I just want to process the results instead of all the values present flows table to just values which are greater range i.e 50. I want to know what level these two queries are not the same. Thanks.

score 5 · Accepted Answer · edited Aug 16 '14 at 21:58

You are doing a sum() which means you "lose" the original values -- they are aggregated.

By applying a where clause, you filter rows before the aggregation phase.

I suspect what you want is a having clause, something like:

select sourceIP, sum(sourceBytes) 
from flows
group by sourceIP
having sum(sourceBytes) > 50 
order by sum(sourceBytes) desc;

Having is like a where but applied to the aggregated results.

Note that you can also use a subquery and a where instead of having as follows:

select * from (
  select sourceIP, sum(sourceBytes) as sum_sourceBytes 
  from flows
  group by sourceIP
) a
where sum_sourceBytes > 50
order by sum_sourceBytes desc;

SQL where clause effect on table?

1 Answers1