On Fri, Oct 21, 2005 at 01:53:38PM +0800, rick wrote:
> anyways, this is what i want... i have data of transactions with time and
> date. Each transaction is identified by a unique id. now i want to get the
> number of transactions per hour and the number of transactions per month. of
> course, when all values from both are summed, they should equal to the
> overall number of transactions. unfortunately, i am not able to get
> consistent results... of count....
>
> doing the counts / same queries on small number of records seems not to be a
> problem..
Really? If so, then you might have hit a bug.
> -- get per hour breakdown
> select hour(t_time) as a, count(distinct column1) from am group by a;
>
> -- get per month breakdown
> select month(d_date2) as a, count(distinct column1) from am group by a;
Try the SQL below. It should return 1 regardless of NULLs. Otherwise,
you should start migrating away from that mysql version. :-)
SELECT (SELECT SUM(f1)
FROM (SELECT COUNT(*) AS f1
FROM am
GROUP BY hour(d_date2)) AS t1) =
(SELECT SUM(f2)
FROM (SELECT COUNT(*) AS f2 FROM am
GROUP BY MONTH(d_date2)) AS t2)
AND (SELECT SUM(f1)
FROM (SELECT COUNT(*) AS f1
FROM am
GROUP BY hour(d_date2)) AS t1) =
(SELECT COUNT(*)
FROM am);
Apparently, subquery support in v4.1 still sucks; that can still be
reduced in postgresql.
HTH
--
$_=q:; # SHERWIN #
70;72;69;6e;74;20;
27;4a;75;73;74;20;
61;6e;6f;74;68;65;
72;20;50;65;72;6c;
20;6e;6f;76;69;63;
65;27;:;;s=~?(..);
?=pack q$C$,hex$1;
;;;=egg;;;;eval;;;
_________________________________________________
Philippine Linux Users' Group (PLUG) Mailing List
[email protected] (#PLUG @ irc.free.net.ph)
Read the Guidelines: http://linux.org.ph/lists
Searchable Archives: http://archives.free.net.ph
