Uhm... That's okay if it's microsoft Access... Generally NOW() is not an
sql server function.
Also, if your report spans more than 12 months, you want to use yyyymm as
the format. You get the order correct that way.
There's probably a million ways to get that, but this works:
SELECT Cust_ID,
cast(datepart(yyyy, Order_Date) as nvarchar(4)) +
right('0' + cast(datepart(mm, Order_Date) as nvarchar(2)),
2)
as Yearmonth,
SUM(Order_Total) AS Total
FROM Orders
GROUP BY Cust_ID,
cast(datepart(yyyy, Order_Date) as nvarchar(4)) +
right('0' + cast(datepart(mm, Order_Date) as nvarchar(2)),
2)
-----Original Message-----
From: Dina Hess [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 29, 2002 11:40 AM
To: CF-Talk
Subject: Re: SQL Question
bud,
try this:
SELECT Cust_ID, Order_Date, Month(Order_Date) AS Order_Month,
SUM(Order_Total]) AS Total
FROM Orders
GROUP BY Month(Order_Date), Cust_ID, Order_Date
HAVING Year(Order_Date) = Year(Now());
~ dina
----- Original Message -----
From: "Bud" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, April 29, 2002 11:48 AM
Subject: OT: SQL Question
> Howdy all. I have a client that wants me to build him a
reporting
> section based on the order month. I know how to do a SQL to get
like
> the total of orders for a particular customer.
>
> SELECT Cust_ID, SUM(Order_Total) AS Total
> FROM Orders
> GROUP BY Cust_ID
>
> But, is there a way to group on the month? Something like:
>
> SELECT Cust_ID, Order_Date, SUM(Order_Total) AS Total
> FROM Orders
> GROUP BY Cust_ID, DatePart(Month)
>
> Thanks.
> --
>
> Bud Schneehagen - Tropical Web Creations
>
> _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
> ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED]
> http://www.twcreations.com/
> 954.721.3452
>
_________________________________________________________________
_____
> Signup for the Fusion Authority news alert and keep up with the
latest news in ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
> FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
> Archives:
http://www.mail-archive.com/cf-talk@houseoffusion.com/
> Unsubscribe:
http://www.houseoffusion.com/index.cfm?sidebar=lists
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
