CODEWITHSUNDEEP
sqlpostgresql
duckman
duckman

Reputation: 747

SQL: average by month

I would like to calculate the monthly average per venue for a variable which has daily data. My date variable has a numeric format "YYYYMMDD". My data looks like this:

date        venue   metric
20150203    1       0.78
20150204    1       0.65
20150205    1       0.90
20150206    1       0.40
20150203    2       0.74
20150204    2       0.77
20150205    2       0.88
20150206    2       0.64
20150203    3       0.78
20150204    3       0.92
20150205    3       0.94
20150206    3       0.60

Upvotes: 0

Views: 3013

Answers (3)

SandPiper
SandPiper

Reputation: 2906

Try this:

SELECT SUBSTRING(DATE, 5,2) AS MONTH, VENUE, AVG (METRIC)
FROM TABLENAME
GROUP BY SUBSTRING(DATE, 5,2), VENUE

Also, I agree with everyone here on you should store dates as a date value.

Upvotes: 1

Michel Milezzi
Michel Milezzi

Reputation: 11175

Assuming that you're using PostgreSQL:

SELECT
    month,
    venue,
    AVG(metric)
FROM (
    SELECT 
        EXTRACT(MONTH FROM TO_DATE(CAST(your_date_field AS TEXT), 'YYYYMMDD')) AS month, venue, metric
    FROM 
        your_table
    ) AS sub
GROUP BY
    month, venue;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You should be storing dates as dates not as numbers, regardless of the database. In any case, something like the following should work in any database:

select floor(date / 100) as yyyymm, venue, avg(metric)
from t
group by floor(date / 100), venue;

Upvotes: 1

Related Questions