please help with mysql query string

[Ljoha]

I need to create report where will be shown total quantity of all rows
for every hour in some data range.

I have a table where I have column in DATETIME format. I have problem
to create sql query where I have to get result of the all counts for
every hour of the date range.
Something like that:
from starting hour to final hour = total row count (if there is an
entries),
from 01:00 to 02:00 = 5,
from 02:00 to 03:00 = 0,
.........
from 23:00 to 00:00 = 8, between 2005-01-01 and 2005-01-31.

I can get result for one hour but how to get results for all hours? I
am newbie in SQL....

SELECT count(MyTable.MyColumn) as '15:00-16:00' from MyDB.MyTable WHERE
hour(MyTable.MyColumn) like '%15%' AND MyTable.MyColumn BETWEEN
'2005-05-01 00:00:00' AND '2005-05-31 23:59:59'

Any help?

[Bill Karwin]

Ljoha wrote:

I can get result for one hour but how to get results for all hours? I
am newbie in SQL....

SELECT count(MyTable.MyColumn) as '15:00-16:00' from MyDB.MyTable WHERE
hour(MyTable.MyColumn) like '%15%' AND MyTable.MyColumn BETWEEN
'2005-05-01 00:00:00' AND '2005-05-31 23:59:59'

The best way to do what you want in SQL is to use the GROUP BY clause.

SELECT HOUR(MyTable.MyColumn) AS Hour, COUNT(MyTable.MyColumn) AS Count
FROM MyDB.MyTable
GROUP BY HOUR(MyTable.MyColumn);

Regards,
Bill K.

[Ljoha]

Bill, thank you for your replay!

With this query, if my table has no row for example for hour 13:00
-14:00, I can't get result. In this query I will get only that
hours that has rows for certain time period. Is it possible to get all
24 hours range and if some hour doesn't have a value it will be with
zero in result?

[Bill Karwin]

Ljoha wrote:

Bill, thank you for your replay!

With this query, if my table has no row for example for hour 13:00
-14:00, I can't get result. In this query I will get only that
hours that has rows for certain time period. Is it possible to get all
24 hours range and if some hour doesn't have a value it will be with
zero in result?

It's tricky to get GROUP BY to create groups for values that do not
occur in your table. To do this, you need to have a list available of
all the values for the group that you need to report.

For instance, create a temporary table containing the values of all hours:

CREATE TEMPORARY TABLE hours (hr INTEGER);
INSERT INTO hours 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);

Now join that hours table to your records in MyTable using a LEFT OUTER
JOIN, so you'll get the hour number even if there are no matching records.

SELECT hours.hr, COUNT(DISTINCT MyTable.MyColumn) AS Count
FROM hours LEFT OUTER JOIN MyTable ON hours.hr = HOUR(MyTable.MyColumn)
GROUP BY hours.hr;

The COUNT(DISTINCT expr) gives the number of distinct non-null values in
the expression. So if there are only NULLs (that is, no rows in MyTable
match hour.hr) the result should be zero.

Read more about COUNT at:
http://dev.mysql.com/doc/mysql/en/gr...functions.html

Read more about temporary tables at:
http://dev.mysql.com/doc/mysql/en/create-table.html

Read more about using LEFT OUTER JOIN in a SQL book of your choice.

Regards,
Bill K.

[Ljoha]

Thanks, working great! But unfortunately I need this query only if I
can limit this query with date range. I tried to put in your query
"BETWEEN '2005-06-01 00:00:00' AND '2005-06-07 23:59:59' " - but it
doesn't work... :-(
this is for analysing - one week with another, or one month with
another....

[Bill Karwin]

Ljoha wrote:

Thanks, working great! But unfortunately I need this query only if I
can limit this query with date range. I tried to put in your query
"BETWEEN '2005-06-01 00:00:00' AND '2005-06-07 23:59:59' " - but it
doesn't work... :-(

Probably depends where you put it! :-)

When using outer joins, and making restrictions on the side of the join
that might be unmatched, add the conditions to the join condition,
instead of in a WHERE clause.

SELECT hours.hr, COUNT(DISTINCT MyTable.MyColumn) AS Count
FROM hours LEFT OUTER JOIN MyTable
ON hours.hr = HOUR(MyTable.MyColumn) AND
MyTable.MyColumn BETWEEN '2005-06-01 00:00:00' AND '2005-06-07
23:59:59'
GROUP BY hours.hr;

If you were to put the BETWEEN condition it in a WHERE clause, the hours
for which the right side of the join resulted in NULL don't match the
BETWEEN condition, so those rows are eliminated from the result set, and
you'll have no entries to count for certain hours. If you put the
condition in the join clause, you limit the possible records from
MyTable that might match, without eliminating the hours.

Regards,
Bill K.

[Ljoha]

Bill, thank you soooo much! You are great profi!
I have to study a little with sql query :-)