SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist priti's Avatar
    Join Date
    Aug 2006
    Location
    India
    Posts
    488
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to find count for every hour in mysql

    Hi,

    I want to write a query where in i have to show count of data entered by every hour .

    i.e
    hours
    (from midnight to mid-night)

    00-01 : how many counts
    01-02 : ''
    02-03 : "

    :
    :
    :
    22-23:"
    23-24:"

    any one know how to find i did a trick but i have to fire 24 queries for it specific for hour interval.

    Can a single query do this job - group by,having ???

    Thanks

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select extract(hour from yourDateColumn) as theHour,
             count(*) as numberOfItems
      from yourTable
     group by extract(hour from yourDateColumn)

  3. #3
    SitePoint Evangelist priti's Avatar
    Join Date
    Aug 2006
    Location
    India
    Posts
    488
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    00-01 : how many counts
    01-02 : ''
    02-03 : "

    :
    :
    :
    22-23:"
    23-24:"

    this is "Daily" kinds

    yourDateColumn - there is no field Every day has 24 hours and i want to check what is the lead count between evry hours starting from mid-night.

    I hope i make some sense !!

    Thanks

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    No, it does not make sense. Give some sample data and the expected result.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    priti, swampBoogie's solution looks good to me.
    If you don't understand how it works, and can't adapt it to fit your situation, then please post your table structure, some example data, and the query you got so far.

  6. #6
    SitePoint Evangelist priti's Avatar
    Join Date
    Aug 2006
    Location
    India
    Posts
    488
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    consider

    submitted transaction_k

    2009-01-27 00:00:12 abc1233
    2009-01-27 01:00:12 abc1234
    2009-01-27 02:00:12 abc1234
    2009-01-27 03:00:12 abc1294
    2009-01-27 04:00:12 abc1234
    2009-01-27 04:40:12 abc1234
    2009-01-27 05:00:12 abc1453
    2009-01-27 06:00:12 abc1232
    2009-01-27 07:00:12 abc1634
    2009-01-27 07:00:12 abc1736

    now
    hour-count
    00-01 - 1
    01-02 - 1
    02-03-1
    03-04 -1
    04-05 - 2 count

    start hour-end hour
    00-01
    01-02
    03-04
    05-06

    ;

    ;

    likewise i will have 24 entries in my table.It's a cron job so at mid night it will start and count how many (transaction_k) count are received in particular time range .

    Now it will be difficult to be more precise then this .

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    So... what is wrong with the query swampBoogie posted?

  8. #8
    SitePoint Evangelist priti's Avatar
    Join Date
    Aug 2006
    Location
    India
    Posts
    488
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That query is gr8 and work if no specific date is given.

    SELECT extract( HOUR
    FROM submitted ) AS theHour, count( * ) AS numberOfItems
    FROM table
    WHERE date_format( `submitted` , '%d-%m-%Y' ) = '24-01-2009'
    GROUP BY extract( HOUR
    FROM submitted )


    this fetch only those record for which i have transaction rest of time interval sud show me count 0.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT h.theHour
         , COUNT(submitted) AS numberOfItems
      FROM ( SELECT 0 AS theHour
             UNION ALL SELECT 1   
             UNION ALL SELECT 2 
             UNION ALL SELECT 3 
             UNION ALL SELECT 4 
             UNION ALL SELECT 5 
             UNION ALL SELECT 6 
             UNION ALL SELECT 7 
             UNION ALL SELECT 8 
             UNION ALL SELECT 9 
             UNION ALL SELECT 10 
             UNION ALL SELECT 11 ) AS h
    LEFT OUTER
      JOIN table
        ON EXTRACT(HOUR FROM table.submitted) = h.theHour
       AND DATE(table.submitted) = '2009-01-24'
    GROUP 
        BY h.theHour
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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