Web Analytics Made Easy -
StatCounter COUNT by date - CodingForum

Announcement

Collapse
No announcement yet.

COUNT by date

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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
    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

    Comment


    • #3
      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.

      Comment


      • #4
        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

        Comment


        • #5
          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.
          Be yourself. No one else is as qualified.

          Comment


          • #6
            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

            Comment


            • #7
              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.
              Be yourself. No one else is as qualified.

              Comment


              • #8
                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

                Comment


                • #9
                  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 ~
                  Be yourself. No one else is as qualified.

                  Comment


                  • #10
                    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?

                    Comment


                    • #11
                      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

                      Comment


                      • #12
                        that part is handled in your application code and is a question for the php forum.

                        Comment


                        • #13
                          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)";
                          Be yourself. No one else is as qualified.

                          Comment

                          Working...
                          X