why I can't group by an expression

Hello, I want to create a summary table aggregating the sales based on the month and region. But in my base table, there is only a date field, I have to use month(date) to get the month, but I can't use "group by region, month(date)" to create the summary table. My SQL looks like: insert into sum_mon_reg select region, sum(sales) sum_sales, month(date) date from sales group by region, month(date) then system reports "syntax error" What is the simplest way to solve this problem? I know I could add another table with two fields:date and their corresponding month to solve this. But any simpler way? Your help will be most appreciated. -Xiang -- Posted using Reference.COM http://www.reference.com Browse, Search and Post Usenet and Mailing list Archive and Catalog. InReference, Inc. accepts no responsibility for the content of this posting.