...

View Full Version : COUNT by date



leroybobbins
04-01-2009, 12:18 AM
Hello,

I'm a total beginner. I've built a database that stores leads submitted through a form. The leads have a leadID, leadInfo, and timestamp.

I'm now creating reports. I created a page that shows all the leads in descending order so I can see the total number of leads. I also learned how to use COUNT to count the leadIDs.

So, here is my question:
How do I count leadID's and sort them by specified time periods? I want to see Today's Leads, This Week's Leads, This Month's Leads, and Total Leads (I know how to count total leads).

Any help is much appreciated.

Thanks,
Leroy

bazz
04-01-2009, 12:39 AM
The way I would begin to work through this is to think about what you will need to tell the script. the 'a href' to go to the script needs to show the start_point and end_point of your report period or perhaps just one point with a conditional.

Imagining you have given it a start point and an end point, you then could perform your query with the following conditional



where timestamp >=start_point
and timestamp <= end_point


you would need to convert your start_point and end point into timestamp to perform the comparison check, whether you are calling a week's, a month's or a years records.


hth

bazz

leroybobbins
04-01-2009, 12:52 AM
I think I can use a BETWEEN but I want to know how to identify the date. Then use that variable in the BETWEEN.

I hope that makes sense. Like I said I am just beginning.

bazz
04-01-2009, 12:55 AM
yeh I forgot about BETWEEN. you can use it like this (once you have converted your start and end to timestamp),



where timestamp BETWEEN start_point and end_point


bazz
ps I am only a mysql beginner too.

Old Pedant
04-01-2009, 02:26 AM
The problem with this simple answer is that you could only use ONE such WHERE per SELECT, to get one kind of count. If you wanted a count for today, for a week, for a month, for a year, you'd have to do four separate SELECTs.

Now, you could use a UNION in order to return all the results at once, but still...4 selects.

Maybe something like:


SELECT 1 as numberofdays, count(*) from table where Date(timestamp) = current_date
UNION
SELECT 7 as numberofdays, count(*) from table where Date(timestamp) >= current_date - 6
UNION
SELECT 30 as numberofdays, count(*) from table where Date(timestamp) >= current_date - 29

or similar.

If you want to get all the counts in a single record, you can do it sneakily:


SELECT SUM( IF(Date(timestamp)=current_date,1,0) ) AS countForToday,
SUM( IF(Date(timestamp)>=current_date-6,1,0) ) AS countForWeek,
SUM( IF(Date(timestamp)>=current_date-29,1,0) ) AS countForMonth,
COUNT(*) AS countForYear
FROM table
WHERE Date(timestamp) >= current_date-364

Or similar code.

bazz
04-01-2009, 02:51 AM
Ah, I hadn't picked up that he wanted to do them 'all in one go'. I mistook the plan to be viewing them one category at a time.

bazz

Old Pedant
04-01-2009, 05:49 AM
I'm not sure he does want them "all in one go." My comment was more of a "if you want them all in one go..." Sorry I wasn't clear on that.

leroybobbins
04-02-2009, 11:20 PM
SELECT 1 as numberofdays, count(*) from table where Date(timestamp) = current_date


Thank you Old Pedant for the help. I don't fully understand your solution yet.

The timestamp is in this format: YYYY-MM-DD HH:MM:SS

How do I get the current_date?

Can you show me the exact query needed?

Thanks,
Leroy

Old Pedant
04-02-2009, 11:35 PM
I thought I did show you the exact query.

DATE() is a builtin function in MySQL that strips the TIME portion off of a DATETIME value. That is


DATE('2009-03-31 09:03:01') ==>> '2009-03-31'

And current_date is another MySQL built-in keyword that gives you--what else?--the current date. Today.

So that code


SELECT 1 as numberofdays, count(*) from table where Date(timestamp) = current_date

is saying:
"Get the constant value 1 and put it into a field named numberofdays and then get the count of all records that match the following condition:
where the timestamp field, when stripped of its time value so it becomes only a date, is the same as today."

Note that timestamp is a keyword in MySQL, so possibly you would need to put `...` around that field name. And optionally, you can give a name to count(*).

So perhaps:


SELECT 1 as numberofdays, count(*) as howmany from table where Date(`timestamp`) = current_date

Those are "backticks" in there, *NOT* apostrophes. The character that usually is on the same key as ~

leroybobbins
04-03-2009, 02:33 AM
This is what I used for the code:

$query = "SELECT 1 as numberofdays, count(*) FROM leads WHERE Date(`leadStamp`) = current_date";

$result = mysql_query($query) or die(mysql_error());

echo "There are $result leads today";

But this is what it is printing:
There are Resource id #2 leads today

What am I doing wrong?

leroybobbins
04-03-2009, 03:42 AM
I got it to show the number of leads for the current day with this code:


$query = "SELECT leadID, COUNT(leadID) FROM leads WHERE Date(`leadStamp`) = current_date";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(leadID)'] ." leads today. ";
echo "<br />";
}



Now I would like to have it count all the leads and order them by date and put them in a table.
For example:
Date Leads
3-30-2009 543
4-1-2009 765
4-2-2009 356

guelphdad
04-03-2009, 06:19 PM
that part is handled in your application code and is a question for the php forum.

Old Pedant
04-04-2009, 07:07 AM
Well, I disagree somewhat with GuelphDad.

You would certainly want to first structure the SQL to *GET* all the days in your needed range:

$query = "SELECT Date(leadStamp), COUNT(leadID) FROM leads WHERE Date(leadStamp) BETWEEN '2009-03-30' AND '2009-04-30' GROUP BY Date(leadStamp) ORDER BY Date(leadStamp)";


Or, if you wanted (say) the last week:

$query = "SELECT Date(leadStamp), COUNT(leadID) FROM leads WHERE Date(leadStamp) BETWEEN CUR_DATE-6 AND CUR_DATE GROUP BY Date(leadStamp) ORDER BY Date(leadStamp)";



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum