asked on

group by month and year

How can I group the following by month and day. I can make it work by months but this groups month in all the years in one record.

SELECT SWD_SCADA_Daily.[Well Name], Sum(SWD_SCADA_Daily.[Water Injection (bbl/day)]) AS CumInjection, Avg(SWD_SCADA_Daily.[Injection Pressure (Psi)]) AS AverageDischargePressure, Avg(SWD_SCADA_Daily.[WHP (Psi)]) AS AverageWHP, MONTH(SWD_SCADA_Daily.[Time/Date]) INTO SWD_SCADA_MONTHLY
FROM SWD_SCADA_Daily
GROUP BY SWD_SCADA_Daily.[Well Name], MONTH(SWD_SCADA_Daily.[Time/Date]);

Thank you.

Tomas Helgi Johannsson

Hi,

Move the MONTH(SWD_SCADA_Daily.[Time/Date] colum as the second column in the select.

Regards,
Tomas Helgi

Scott Pletcher

n/a

Aiysha

ASKER

I want to group month and year. Not just the month.

Scott Pletcher

n/a

Aiysha

ASKER

Undefined function 'CONVERT' in expression.

Scott Pletcher

SORRY, I was thinking SQL Server, didn't see the Access tag. I'll remove that and let someone who knows Access comment.

Aiysha

ASKER

I know i have to use format but just struggling with complete query setup

Paul Cook-Giles

Add a new column: Year(SWD_SCADA_Daily.[Time/Date]) (think you'll need to add it to the group by as well).

If you want both year and month in the same column, use MONTH(SWD_SCADA_Daily.[Time/Date]) & " " & Year(SWD_SCADA_Daily.[Time/Date])

Aiysha

ASKER

I think this should work but I get type mismatch error

SELECT SWD_SCADA_Daily.[Well Name], Sum(SWD_SCADA_Daily.[Water Injection (bbl/day)]) AS CumInjection, Avg(SWD_SCADA_Daily.[Injection Pressure (Psi)]) AS AverageDischargePressure, Avg(SWD_SCADA_Daily.[WHP (Psi)]) AS AverageWHP,

Format([Time/Date],"mmm","yyyy") AS Expr1

INTO SWD_SCADA_MONTHLY
FROM SWD_SCADA_Daily
GROUP BY SWD_SCADA_Daily.[Well Name],

Format([Time/Date],"mmm","yyyy");

ASKER CERTIFIED SOLUTION

Paul Cook-Giles

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aiysha

ASKER

Thank you so much Paul !!

Paul Cook-Giles

of course! :)