Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Group by Date, not DateTime 1

Status
Not open for further replies.

TheCandyman

Technical User
Sep 9, 2002
761
US
I'm trying to get a query to group records by the day and not by each time on that date. Here is some data i'm working with

Code:
RegDate	                SumOfAmountPaid
8/2/2010 10:12:59 AM	$50.35
8/3/2010 1:19:54 PM	$302.00
8/3/2010 2:32:24 PM	$25.00
8/5/2010 9:37:04 AM	$31.65
8/5/2010 3:07:45 PM	$36.20
8/9/2010 9:44:41 AM	$35.15
8/12/2010 9:37:05 PM	$50.00
8/16/2010 9:40:46 AM	$26.05
8/16/2010 9:43:17 AM	$31.30

The SQL i'm trying to use is:
Code:
SELECT tbl_Clas_Ads.RegDate, Sum(tbl_Payment.AmountPaid) AS SumOfAmountPaid
FROM tbl_Clas_Ads INNER JOIN tbl_Payment ON tbl_Clas_Ads.Ad_ID = tbl_Payment.Ad_ID
WHERE (tbl_Payment.TransID Is Not Null)
GROUP BY tbl_Clas_Ads.[RegDate]
HAVING ((tbl_Clas_Ads.[RegDate]>=#8/1/2010#) And (tbl_Clas_Ads.[RegDate]<#9/1/2010#));

I'm hitting this from a web page (ASP), so I have tried a few suggestions i found while Googleing, but couldn't find anything that would work. I know there is the Format(XXX,"short date") which only works within MS Access but not from a page running ASP. Any ideas how to get this to work?

 
GROUP BY Int(tbl_Clas_Ads.[RegDate])
 
I tried your suggestion:
Code:
SELECT tbl_Clas_Ads.RegDate, Sum(tbl_Payment.AmountPaid) AS SumOfAmountPaid FROM tbl_Clas_Ads INNER JOIN tbl_Payment ON tbl_Clas_Ads.Ad_ID = tbl_Payment.Ad_ID WHERE (tbl_Payment.TransID Is Not Null) GROUP BY Int(tbl_Clas_Ads.RegDate) HAVING ((tbl_Clas_Ads.[RegDate]>=#8/1/2010#) And (tbl_Clas_Ads.[RegDate]<#9/1/2010#));


But i get this error:
You tried to execute a query that does not include the specified expression 'RegDate' as part of an aggregate function.


As soon as i remove the 'Int' from the SQL it works again. The Int is an ASP command, not a SQL one i believe. Any other suggestions?
 
SELECT Int(A.RegDate) AS RegDay, Sum(P.AmountPaid) AS SumOfAmountPaid
FROM tbl_Clas_Ads A INNER JOIN tbl_Payment P ON A.Ad_ID = P.Ad_ID
WHERE P.TransID Is Not Null AND Int(A.RegDate) Between #2010-08-01# And #2010-08-31#
GROUP BY Int(A.RegDate)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
That worked! Plus, i learned about the 'Int' command too.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top