[ciapug] SQL Question

[Tim Perdue]

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
>