RE: why I can't group by an expression
try insert into sum_mon_reg
select region,
sum(sales) sum_sales,
month(date) date
from sales
group by 1, 3
}-----Original Message-----
}From: xiangz@rocketmail.com [SMTP:xiangz@rocketmail.com]
}Sent: Tuesday, August 26, 1997 1:41 AM
}To: informix-list@rmy.emory.edu
}Subject: 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.