getting months from days

Hi,

I'm using MySQL 5.0. If I have a table, DAILY_SALES, with columns

DAY DATE,
SALES FLOAT UNSIGNED,
USER_ID INTEGER,
PRIMARY KEY (DAY)

How would I write a query that given a user id and date range, returns
monthly sales totals? So, if the date range were '2006-01-01' to
'2006-12-31', my query result set would have twelve rows of data (and
you can assume there is at least one sale per month).

Thanks for your help, - Dave

>I'm using MySQL 5.0. If I have a table, DAILY_SALES, with columns
>
>DAY DATE,
>SALES FLOAT UNSIGNED,
>USER_ID INTEGER,
>PRIMARY KEY (DAY)
>
>How would I write a query that given a user id and date range, returns
>monthly sales totals? So, if the date range were '2006-01-01' to
>'2006-12-31', my query result set would have twelve rows of data (and
>you can assume there is at least one sale per month).

This is somewhat clunky, but left(day,7) gives you the year and the month
without the day part. You can GROUP BY this. Also, I think DAY is a
keyword.

SELECT concat(left(`DAY`,7), '-01'), sum(SALES), USER_ID FROM DAILY_SALES
WHERE USER_ID = whatever GROUP BY left(`DAY`,7)

gordonb.e1mm4@burditt.org (Gordon Burditt) wrote:
>>I'm using MySQL 5.0. If I have a table, DAILY_SALES, with columns
>>
>>DAY DATE,
>>SALES FLOAT UNSIGNED,
>>USER_ID INTEGER,
>>PRIMARY KEY (DAY)
>>
>>How would I write a query that given a user id and date range, returns
>>monthly sales totals? So, if the date range were '2006-01-01' to
>>'2006-12-31', my query result set would have twelve rows of data (and
>>you can assume there is at least one sale per month).
>
> This is somewhat clunky,

More than that.

> but left(day,7) gives you the year and the month
> without the day part.

The problem is, that you rely on the string representation of a DATE
column. This representation may change in the future. Many users asked
for a configurable default_date_format to be used for the automatic
conversion, so sooner or later this will come. So your query might work
or not, depending on the MySQL version and configuration.

After all, a DATE is a DATE and no string. There are functions around
to efficiently deal with DATE values. I.e. DATE_FORMAT('%Y-%m', `DAY`)
would have been a correct way to extract year and month from the `DAY`
column. IMNSHO it's even better to handle year and month as separate,
numeric entities:

SELECT YEAR(`DAY`) AS year,
MONTH(`DAY`) AS month,
SUM(SALES) AS total
FROM DAILY_SALES
WHERE USER_ID = ...
AND `DAY` BETWEEN ... AND ...
GROUP BY year, month


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/