Professional Web Applications Themes

getting months from days - MySQL

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...

  1. #1

    Default 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

    laredotornado@zipmail.com Guest

  2. #2

    Default Re: getting months from days

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

    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)

    Gordon Guest

  3. #3

    Default Re: getting months from days

    org (Gordon Burditt) wrote: 
    >
    > This is somewhat clunky,[/ref]

    More than that.
     

    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/
    Axel Guest

Similar Threads

  1. #39560 [NEW]: Inconsistent behaviour of strtotime when days > days in month
    By php at colin dot guthr dot ie in forum PHP Bugs
    Replies: 1
    Last Post: November 20th, 05:21 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139