[ciapug] SQL Question

[Tim Perdue]

Dave J. Hala Jr. wrote:
> In mysql 3.2x, the having statement is similiar to where, but its
> applied after the query results have been selected.  I'm assuming that
> with the "where" the condition is applied *before* the grouping?
> 
> Using where, i get  984 results returned:
> 
> SELECT id, afn,fnm,lnm,dat,prgdsc,svc,cor,cos,zip,cit FROM `snapshot` 
> WHERE svc='L1AA' and cit='Marshalltown'
> GROUP BY afn,svc
> ORDER by afn
> limit 50000;
> 
> Using having, I also get 984 results returned.
> 
> 
> SELECT count(*), id, afn,fnm,lnm,dat,prgdsc,svc,cor,cos,zip,cit FROM
> `snapshot` 
> GROUP BY afn,svc
> having svc='L1AA' and cit='Marshalltown'
> ORDER by afn
> limit 50000;
> 
> The reason I grouped on 'afn' and 'svc' is that I only a set of records
> that had with an unduplicated value in the afn field and that unique
> 'L1AA' value. This query returns the same results every time.

I don't think it will if the other fields have multiple rows with 
non-unique data in them. Running it 5 times in a row may get the same 
results, but it won't be predictable over time. Try inserting some data 
into the other columns, running it, then dumping/restoring the db. 
Different results are possible. There's no way it could be consistent 
since the columns that you *didn't* specify in the "Group by" would have 
to be randomly dropped out if they don't group together right.

I think "HAVING" is really meant to return results AFTER an aggregation 
function.

like "HAVING xsum > 50" That means the HAVING is applied after the 
grouping so you can further limit it down. As it is, you're just 
treating it like a super expensive way of doing a WHERE clause.

> Your query, (slightly modified) also returns 984.
> 
> SELECT afn,svc
>  FROM snapshot
>  WHERE svc='L1AA' and cit='Marshalltown'
>  GROUP BY afn,svc;