CODEWITHSUNDEEP
sqlms-access
assutu
assutu

Reputation: 35

Access SQL- Count distinct months from Table 1 by City in Table 2

I have a table (Orders) featuring CustomerID, Product, Sales, and Date (yyyy-mm-dd).

CustomerID is linked to the Customer table at CustomerName. The Customer table has fields Customername, City, State, Country.

I would like to count the number of unique months for the calendar year which an ordered occurred by each City.

Unfortunately, so for I've only been able to count distinct months across all Customers and Dates. How can I tie it to each city in the Customer table?

SELECT Count(*) AS CountOfMonths
FROM (
SELECT DISTINCT Month(Date), Year(Date)
FROM Orders );

Thanks!

Upvotes: 0

Views: 189

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

Simply join the tables and aggregate by year and city and count distinct months:

select c.country, c.state, c.city, year(o.date), count(distinct month(o.date))
from orders o
join customers c on c.customername = o.customerid
group by c.country, c.state, c.city, year(o.date)
order by c.country, c.state, c.city, year(o.date);

Add a WHERE clause, if you want to see one year only.

UPDATE: MS Access doesn't support COUNT(DISTINCT expresssion). So you need a distinct subquery there:

select c.country, c.state, c.city, order_year, count(*)
from
(
  select distinct c.country, c.state, c.city, year(o.date) as order_year, month(o.date)
  from orders o
  join customers c on c.customername = o.customerid
) as dist_months
group by country, state, city, order_year
order by country, state, city, order_year;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270763

Something like this:

select country, city, state,
       count(*) as NumDistinctMonths
from (select c.country, c.state, c.city,
             year(o.date) as yyyy, month(o.date) as mm, count(*) as cnt
      from orders as o inner join
           customers as c
           on o.CustomerID = c.CustomerId
      group by c.country, c.state, c.city, year(o.date), month(o.date)
     ) as ccsym
group by country, city, state;

Upvotes: 0

Related Questions