MySQL Forums
Forum List  »  Newbie

Re: Searching for shortcuts, can anyone help!!
Posted by: Felix Geerinckx
Date: July 06, 2005 08:52AM

Deon Starkey wrote:

> This is the format I wish to return the results in:
>
> Column headings
> 1-160 161-320 321-480 481-640 641-800 801-960 961-1000
> number of messages for each character length.

You need a crosstab query:

SELECT
SUM(IF(message_len BETWEEN 1 AND 160, 1, 0)) AS '1-160',
SUM(IF(message_len BETWEEN 161 AND 320, 1, 0)) AS '161-320',
SUM(IF(message_len BETWEEN 321 AND 480, 1, 0)) AS '321-480',
...
FROM table_1
WHERE
timestamp >= '2005-06-01 00:00:00' and timestamp <= '2005-06-30 23:59:59'

See http://dev.mysql.com/tech-resources/articles/wizard/index.html for more info on crosstab queries.

--
felix
Please use BBCode to format your messages in this forum.

Options: ReplyQuote


Subject
Written By
Posted
Re: Searching for shortcuts, can anyone help!!
July 06, 2005 08:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.