To produce a summary that includes all hours of the day, create a reference table that lists each hour : Create Table « Table Index « SQL / MySQL






  1. SQL / MySQL
  2. Table Index
  3. Create Table

To produce a summary that includes all hours of the day, create a reference table that lists each hour

        
mysql>
mysql> CREATE TABLE mail
    -> (
    ->  t               DATETIME,       # when message was sent
    ->  senderUser      CHAR(8),        # sender (source user and host)
    ->  senderHost      CHAR(20),
    ->  recipientUser   CHAR(8),        # recipient (destination user and host)
    ->  recipientHost   CHAR(20),
    ->  size    BIGINT,         # message size in bytes
    ->  INDEX   (t)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO mail (t,senderHost,senderUser,recipientHost,recipientUser,size)
    ->  VALUES
    ->          ('2010-05-11 10:15:08','saturn','barb','mars','tricia',58274),
    ->          ('2010-05-12 12:48:13','mars','tricia','venus','gene',194925),
    ->          ('2010-05-12 15:02:49','mars','phil','saturn','phil',1048),
    ->          ('2010-05-13 13:59:18','saturn','barb','venus','tricia',271),
    ->          ('2010-05-14 09:31:37','venus','gene','mars','barb',2291),
    ->          ('2010-05-14 11:52:17','mars','phil','saturn','tricia',5781),
    ->          ('2010-05-14 14:42:21','venus','barb','venus','barb',98151),
    ->          ('2010-05-14 17:03:01','saturn','tricia','venus','phil',2394482),
    ->          ('2010-05-15 07:17:48','mars','gene','saturn','gene',3824),
    ->          ('2010-05-15 08:50:57','venus','phil','venus','phil',978),
    ->          ('2010-05-15 10:25:52','mars','gene','saturn','tricia',998532),
    ->          ('2010-05-15 17:35:31','saturn','gene','mars','gene',3856),
    ->          ('2010-05-16 09:00:28','venus','gene','mars','barb',613),
    ->          ('2010-05-16 23:04:19','venus','phil','venus','barb',10294),
    ->          ('2010-05-17 12:49:23','mars','phil','saturn','tricia',873),
    ->          ('2010-05-19 22:21:51','saturn','gene','venus','gene',23992);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE ref (h INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ref (h)
    -> VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),
    -> (12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT ref.h AS hour, COUNT(HOUR(mail.t)) AS count
    -> FROM ref LEFT JOIN mail ON ref.h = HOUR(mail.t)
    -> GROUP BY hour;
+------+-------+
| hour | count |
+------+-------+
|    0 |     0 |
|    1 |     0 |
|    2 |     0 |
|    3 |     0 |
|    4 |     0 |
|    5 |     0 |
|    6 |     0 |
|    7 |     1 |
|    8 |     1 |
|    9 |     2 |
|   10 |     2 |
|   11 |     1 |
|   12 |     2 |
|   13 |     1 |
|   14 |     1 |
|   15 |     1 |
|   16 |     0 |
|   17 |     2 |
|   18 |     0 |
|   19 |     0 |
|   20 |     0 |
|   21 |     0 |
|   22 |     1 |
|   23 |     1 |
+------+-------+
24 rows in set (0.00 sec)

mysql>
mysql> drop table mail;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Create Simple Tables
2.Create table: default value for column
3.Create table: small int and unsigned int
4.Create table: smallint, decimal and float
5.Create table: char, varchar
6.Create table: BLOB
7.Create table: char set and language
8.Create table: ENUM and set
9.Create table: YEAR and TIMESTAMP
10.Create table: null and not null
11.Create table: not null and default value
12.Create table: default int value
13.Create table: primary key
14.Create table: primary key 2
15.Create table: two columns for primary key
16.Create table: auto increment primary key
17.Create table: REFERENCES
18.Create table: foreign key
19.Create table: engine type INNODB
20.Set average row length and max, min rows
21.Syntax for Creating Tables (CREATE TABLE)
22.Create an empty table, use a WHERE clause that is always false
23.Creates table-level privileges.
24.Create a copy of the product table:
25.Create a table called cust_names using the table definitions and data from the product table.
26.CREATE TABLE IF NOT EXISTS
27.CREATE TABLE TEAMS_COPY1 LIKE TEAMS
28.Creating Copies of a Table
29.Creating a New Table by Copying
30.Creating a Table
31.Add an IF NOT EXISTS clause to the statement: