Thx barneyb for the hint:
The solution is:
SELECT MONTH(saleDate) AS theMonth, YEAR(saleDate) AS theYear,
sum(total) as dailyTotal
FROM sale
GROUP by MONTH(saleDate), YEAR (saleDate)
ORDER BY YEAR(saleDate), MONTH(saleDate)
-- thx, gil
-----Original Message-----
From: mayo [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 22, 2005 9:52 PM
To: CF-Talk
Subject: RE: group by date, missing something
Thanks eric, but that gives a total for every day.
June 21 .... 10,000
June 20 .... 9,555
June 19 .... 11,200
.....
That's useful but I'm trying to get the total sales for each month and
need to do a group by for the month
June ... 250,000
May ... 430,000
Apr ... 380,000
.....
It should be something like:
SELECT monthpart(datefield) AS xyz
But it's not working. I'm wondering if I'm missing something that is
Microsoft Access specific. (client is using access, can't change that)
:(
Thx
-----Original Message-----
From: eric.creese [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 22, 2005 7:35 PM
To: CF-Talk
Subject: Re: group by date, missing something
SELECT sum(saleTotal), saledate
FROM sales
GROUP BY saledate
----- Original Message -----
From: "mayo" <[EMAIL PROTECTED]>
To: "CF-Talk" <cf-talk@houseoffusion.com>
Sent: Wednesday, June 22, 2005 6:29 PM
Subject: group by date, missing something
> I'm trying to do a group by date on an access db.
>
> I have a list of sales and want to group by month and year. I can't
get
> even a test run to work
>
> SQL below:
>
> SELECT MONTH(saleDate) AS theMonth, sum(saleTotal) as
> dailyTotal
> FROM sales
> GROUP by theMonth
>
> Thx
>
> -- gil
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:210307
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
