Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13

Thread: COUNT by date

  1. #1
    New Coder
    Join Date
    Jan 2009
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts

    COUNT by date

    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

  • #2
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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

    Code:
    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
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #3
    New Coder
    Join Date
    Jan 2009
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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.

  • #4
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    yeh I forgot about BETWEEN. you can use it like this (once you have converted your start and end to timestamp),

    Code:
    where timestamp BETWEEN start_point and end_point
    bazz
    ps I am only a mysql beginner too.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,576
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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:
    Code:
    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:
    Code:
    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.

  • #6
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    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
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,576
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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.

  • #8
    New Coder
    Join Date
    Jan 2009
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,576
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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
    Code:
    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
    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:
    Code:
    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 ~

  • #10
    New Coder
    Join Date
    Jan 2009
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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?

  • #11
    New Coder
    Join Date
    Jan 2009
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

  • #12
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    that part is handled in your application code and is a question for the php forum.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,576
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    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)";

  • Users who have thanked Old Pedant for this post:

    leroybobbins (04-04-2009)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •