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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Aggregate SQL Count Problem

Status
Not open for further replies.

sharkchaser

Technical User
Mar 19, 2010
50
US
I'm quite ingnorant regarding aggregate SQL functions, especially Count functions.

This query returns values that I can then count but what I would like is the query to actually COUNT the number of ListingDate and PendingDates that are returned for each.

Code:
SELECT June2010Newsletter.City, June2010Newsletter.ListingDate, June2010Newsletter.PendingDate
FROM June2010Newsletter
WHERE (((June2010Newsletter.City)="laguna woods") AND ((June2010Newsletter.ListingDate)>=#3/1/2010# And (June2010Newsletter.ListingDate)<=#3/31/2010#)) OR (((June2010Newsletter.City)="laguna woods") AND ((June2010Newsletter.PendingDate)>=#3/1/2010# And (June2010Newsletter.PendingDate)<=#3/31/2010#));

Thanks . . . Rick
 
Code:
SELECT
   Count(*) AS TotalCount,
   Sum(Iif(ListingDate Between #3/1/2010# AND #3/31/2010#, 1, 0)) As CountByListingDate,
   Sum(Iif(PendingDate Between #3/1/2010# AND #3/31/2010#, 1, 0)) As CountByPendingDate
FROM June2010Newsletter AS L
WHERE
   (L.City = "laguna woods")
   AND (
      (ListingDate Between #3/1/2010# AND #3/31/2010#)
      OR (PendingDate Between #3/1/2010# AND #3/31/2010)
   )
 
Thanks Emtucifor . . .

error upon execution:

syntax error in date and query expression

(L.City = "laguna woods")
AND (
(ListingDate Between #3/1/2010# AND #3/31/2010#)
OR (PendingDate Between #3/1/2010# AND #3/31/2010)
)

I can't see it!

Rick
 
I found it!

The last date string was missing the # at the end.

Now I'm going to test it and see.

Thanks MUCH!

Rick
 
Emtucifor . . .

This is a count that I'm getting.

TotalCount Count New Listings Count New Pendings
360 14 0

Shouldn't Count New Listings and Count New Pendings add up to Total Count?

Thanks.

Rick
 
Sorry about the missing octothorpe.

The total should be equal or lower, as low as half the sum of the two, because when both conditions are true at once they get counted only once.

Please show your exact query.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top