Count records with a specific value

[stinkybee]

I have two tables, Table1 and Table2. I need to join these together and return Table1.merchant_name with a count of matching records in Table2. This works fine and returns a count of zero when there are no matching records, which is what I want. However, I also need to specify a date range within the Count which will only count records from Table2 if they are before a specified end date (Table2.end_date).

So far I have this sql which returns what I want without the date range:

SELECT Table1.merchant_name, COUNT(Table2.merchant_name) AS cnt
FROM Table1 
LEFT JOIN Table2 ON Table1.merchant_name = Table2.merchant_name 
WHERE Table1.merchant_name = 'test' 
GROUP BY Table1.merchant_name;

This selects the merchant_name 'test' from Table1 and gives me a count of all records in Table2 that have a matching merchant_name. Now I need to limit the count so that only those records in Table2 that are within my specified date range are added to the count.

For example, if I have 3 matching records in Table2 but only 2 of them have an end_date after todays date I need the Count element to show 2. Or if all 3 are out of the date range then the count element needs to show 0.

Anyone know how I can achieve this?

The date issue is not important, it could just as easily be a specific value but I thought I would highlight my exact problem.

Thanks in advance.

 

[r937]

SELECT Table1.merchant_name
     , COUNT(Table2.merchant_name) AS cnt
  FROM Table1 
LEFT OUTER
  JOIN Table2 
    ON [red](
       Table2.merchant_name = Table1.merchant_name 
   AND Table2.somedate BETWEEN #2010-10-01# AND #2010-10-31#
       )[/red]
 WHERE Table1.merchant_name = 'test' 
GROUP 
    BY Table1.merchant_name

access might not like it without the parentheses, so i included them

and you do realize that if you feed in just one merchant name, that you don't really need to do the join at all, right?

SELECT COUNT(*) AS cnt
  FROM Table2 
 WHERE merchant_name = 'test'
   AND somedate BETWEEN #2010-10-01# AND #2010-10-31#

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[stinkybee]

Thanks for that.

Everything works a treat now.

Sometimes I think maybe I should ask the question before searching the web for several hours trying to find an answer

 

[LarrySteele]

Sometimes I think maybe I should ask the question before searching the web for several hours trying to find an answer

I know what you mean, but I'm still amazed at some of the items I've added to my toolbag by searching.