[ciapug] SQL Question

[Dave J. Hala Jr.]

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.

Your query, (slightly modified) also returns 984.

SELECT afn,svc
 FROM snapshot
 WHERE svc='L1AA' and cit='Marshalltown'
 GROUP BY afn,svc;

Your query is simpler, but I'm still at odds over which  is the best
choice, as they return the same results. Typically, I'd choose simpler.

 

On Mon, 2005-06-13 at 12:17, Tim Perdue wrote:
> Dave J. Hala Jr. wrote:
> > I'm using a grouping sql statement to return a list of unduplicated
> > records:
> > 
> > SELECT id, afn,fnm,lnm,dat,prgdsc,svc,cor,cos,zip,cit,x,y,z FROM
> > `snapshot` 
> > GROUP BY by afn,svc
> > having svc='L1AA' and cit='Marshalltown'
> > ORDER by afn;
> 
> I must not understand why you're using a HAVING when you hadn't yet used 
> a WHERE clause? If that query runs at all, it's got to produce 
> completely random results.
> 
> The way I would typically use GROUP BY is grouping by all the columns 
> except the ones that you are aggregating:
> 
> SELECT id, afn,fnm,lnm,dat,prgdsc,svc,cor,cos,zip,cit,sum(x) AS xsum, 
> sum(y) as ysum, sum(z) as zsum
> FROM snapshot
> WHERE svc='L1AA' and cit='Marshalltown'
> GROUP BY id, afn,fnm,lnm,dat,prgdsc,svc,cor,cos,zip,cit;
> 
> OR
> 
> SELECT afn,svc,sum(x) AS xsum, sum(y) as ysum, sum(z) as zsum
> FROM snapshot
> WHERE svc='L1AA' and cit='Marshalltown'
> GROUP BY afn,svc;
> 
> If you don't put a group by on all the non-aggregate columns, then you 
> are asking mysql to randomly filter out stuff, which is something that 
> it is known to do.
> 
> By using HAVING without WHERE, you're making the DB do all the work of 
> crunching the entire table, then coming back and tossing out records 
> that didn't match.
> 
> > I'd like to sum the x,y,z columns from the result within the same query.
> > This seems like a perfect candidate for a sub-select. However, I'm using
> > mysql 3.x
> > 
> > I could dump the records in a temp table and sum the columns, but I was
> > looking for a way to make the sql server do it in single step. Anyone
> > got any ideas?
> > 
> > Upgrading to mysql 4/5 is out of the question, as well as switching to
> > postgres/MSSQL/Oracle...
> > 
> > 
> > :) Dave
> > 
> 
> _______________________________________________
> ciapug mailing list
> ciapug at cialug.org
> http://cialug.org/mailman/listinfo/ciapug
-- 

Open Source Information Systems (OSIS)
Dave J. Hala Jr. <dave at osis.us>
641.485.1606