Good, you recognize the need to perform two separate aggregates (GROUP
BYs) and compare the separate results... In fact, you may need two temp
tables so that you can (INNER / LEFT) JOIN one to the other. (Yes, I can
think of a query that works without the second temp table but I think it's
kludgey and would rather not post it.)
To find same name and count
CREATE TEMPORARY TABLE tmpCount1
SELECT name, count(1) fq
from table1
WHERE (conditions)
GROUP BY name;
CREATE TEMPORARY TABLE tmpCount2
SELECT name, count(1) fq
from table2
WHERE (conditions)
GROUP BY name;
SELECT t1.name
FROM tmpCount1 t1
INNER JOIN tmpCount2 t2
on t1.name = t2.name
and t1.fq = t2.fq;
DROP TEMPORARY TABLE tmpCount1, tmpCount2;
To find where table2 has MORE than table1
SELECT t2.name
FROM tmpCount2 t2
INNER JOIN tmpCount1 t1
on t1.name = t2.name
WHERE t1.fq < t2.fq;
Does this help in a general way?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Laszlo Thoth <[EMAIL PROTECTED]> wrote on 10/12/2004 01:18:39 PM:
> Quoting [EMAIL PROTECTED]:
>
> > > Why would you want to do that? bananacount is something you can
> > > calculate with a LEFT JOIN and a GROUP BY, so storing it in the DB
> > > would break normalization.
> >
> > This would also the first step in creating your own OLAP cube. For
each
> > statistic, you save yourself a lot of time if you compute the results
of
> > several GROUP BY functions (SUM, AVG, STD, etc) and store those
results
> > into intermediate tables. Then when you want to start slicing and
dicing
> > your data, you do more retrieval and less computations. This seriously
> > improves the performance of your reports. Each new record inserted
into
> > your cube would require you to update every computed statistics table
that
> > covered your new record. But that *is* the trade off, isn't it? Much
> > faster analysis for much slower transaction performance. Thus the
basic
> > difference between OLAP and OLTP.
>
> This is closer to the reason why I'm trying to do this. Obviously this
is an
> example: I'm not actually tracking real monkeys and real banannas with
mysql.
> (Sorry if this disappoints anyone!) In my actual application I'm doing
a JOIN
> between this table and another one with a GROUP BY (a very
differentcriteria)
> and a HAVING clause which selects only members from this other
tablewhich have
> as many elements in this second grouping as appeared in the first
grouping.
>
> So basically I'm trying to compare the results of two different GROUP BY
> statements with a HAVING clause and I'm pretty sure I can't do it all
within
> one statement, so I'm caching the results of one of the GROUPs as a
value in
> one of the tables.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
