CODEWITHSUNDEEP
sqlms-access
user1283776
user1283776

Reputation: 21764

Show sales grouped per month in Access?

I'm trying to show sales grouped per month, to show something like:

201001 10000
201002 13000
201003 11000
201004 8000

Why doesn't this work?

SELECT [Transaction details].[Sales volume LOC]
FROM [Transaction details]
GROUP BY Month([Transaction details].[YYYY-MM-DD]);

I get the error message: "Your query does not include the specified expression 'Sales volume LOC' as part of an aggregate function."

Upvotes: 0

Views: 590

Answers (1)

Taryn
Taryn

Reputation: 247680

The error is because you do not have the column [Sales volume LOC] in an aggregate function or in the GROUP BY clause. If you want the totals for each month then you should add the [Sales volume LOC] to the sum() aggregate function:

SELECT year([Transaction details].[YYYY-MM-DD]) as [Year],
  Month([Transaction details].[YYYY-MM-DD]) as [Month],
  sum([Transaction details].[Sales volume LOC]) as Total
FROM [Transaction details]
GROUP BY year([Transaction details].[YYYY-MM-DD]), Month([Transaction details].[YYYY-MM-DD])
ORDER BY year([Transaction details].[YYYY-MM-DD]), Month([Transaction details].[YYYY-MM-DD]);

Upvotes: 2

Related Questions