CODEWITHSUNDEEP
mysql
Tomm
Tomm

Reputation: 2142

MySQL: Group by Austral year?

Let's say we have a simple table like so

DATE          VALUE
--------------------
2013-01-01    23
2013-06-12    34
2013-07-22    788
2013-12-16    234
2014-01-23    56
2014-04-19    88
2014-09-23    7987
2014-11-05    66
2015-02-17    987
2015-05-05    896

Now let's suppose we want to extract the mean values grouped by year, we would use

SELECT AVG(`VALUE`) FROM tablename GROUP BY YEAR(`DATE`)

However, what if we define the year not from January to December, but rather from July to June (which makes quite a bit of sense for a lot of data if you're living in the Southern hemisphere).

I am sure there must be an easy way to achieve a July YEAR to June YEAR+1 grouping. It just seems to elude me.

-- EDIT --

Awesome! Two good solutions arrived within minutes of posting this question, that both deserved to be accepted. I accepted Roland's solution because of its brevity, but Leo Tara's reply made me aware that I forgot about outputting the associated austral year. So based on both answers I came up with this solution which does exactly what I was asking for:

SELECT 
YEAR(`DATE` - INTERVAL 6 MONTH) as `australyear`,
AVG(`VALUE`) 
FROM tablename 
GROUP BY YEAR(`DATE` - INTERVAL 6 MONTH)

Upvotes: 3

Views: 56

Answers (2)

Joe Taras
Joe Taras

Reputation: 15389

Try this:

SELECT AVG(`VALUE`)
FROM tablename
GROUP BY 
    CASE
        WHEN MONTH('DATE') BETWEEN 1 AND 6 THEN YEAR('DATE') - 1
        ELSE YEAR(`DATE`)
    END

If you want to show year on field list you must rewrite your query in this way:

SELECT
     CASE
        WHEN MONTH('DATE') BETWEEN 1 AND 6 THEN YEAR('DATE') - 1
        ELSE YEAR(`DATE`)
    END, AVG(`VALUE`)
FROM tablename
GROUP BY 
    CASE
        WHEN MONTH('DATE') BETWEEN 1 AND 6 THEN YEAR('DATE') - 1
        ELSE YEAR(`DATE`)
    END

Test on Sql Fiddle

Upvotes: 1

Ronald
Ronald

Reputation: 2882

Just add 6 month to the date when grouping. I don't know the exact syntax in case of mysql, sorry.

Pseudocode:

SELECT AVG(`VALUE`) FROM tablename GROUP BY YEAR(`DATE` + '6 MONTHS');

Upvotes: 4

Related Questions