group by month and year

Aiysha
Aiysha used Ask the Experts™
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

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

Regards,
  Tomas Helgi
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
n/a
Aiysha

Author

Commented:
I want to group month and year. Not just the month.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Attend Free Webinar
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
n/a
Aiysha

Author

Commented:
Undefined function 'CONVERT'  in expression.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

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

Author

Commented:
I know i have to use format but just struggling with complete query setup
Paul Cook-GilesSenior Application Developer

Commented:
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

Author

Commented:
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");
Senior Application Developer
Commented:
you've got an extra argument in your function.  Try this:

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

Author

Commented:
Thank you so much Paul !!
Paul Cook-GilesSenior Application Developer

Commented:
of course!  :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial