i'm still getting inconsistenct overall counts. i have 2117493 records and this should be my overall total.
i changed the query to (from charlton, sherwin and mike's previous pointers...)
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..
:-)
------------------
-- 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;
--------
Date: Thu, 20 Oct 2005 15:32:45 +0100 (BST)
From: Michael Tinsay < [EMAIL PROTECTED]>
Subject: Re: [plug] MySQL count function
To: "The Main Philippine Linux Users' Group (PLUG) Discussion List"
<[email protected] >
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset=iso-8859-1
I think you're being confused by using a GROUP BY on a
DISTINCT column. You will always get 1 as a value for
count. In my previous example, the result I stated
there would be had if you only did a SELECT x,
count(*) GROUP BY x. Issuing a SELECT DISTINCT(x) AS
a, COUNT(*) GROUP BY a, you will always get a value of
1 for the count(*) column, because your effectively
counting 1 row per occurence because of the DISTINCT
keyword.
So for a table with the ff. rows
date time
---- ----
Jan 11am
Jan 12pm
Feb 11am
Feb 10am
A SELECT DISTINCT(date) AS a, count(*) GROUP BY a
would have the result:
Jan 1
Feb 1
But a SELECT DISTINCT(time) AS a, count9*) GROUP BY a
would give you
10am 1
11am 1
12pm 1
Is this close to what you're getting?
--- mike t.
_________________________________________________ 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
