1

The answer to exercise #3 at tech on the net is exactly what I need. However through my own experience and Jensen's answer to a similar question I've found that it doesn't work.

I have data that looks like this:

my_table:
: Customer : Contract : Product : Date    :
: John     : 123      : Plate   : 12/5/13 :
: John     : 123      : Spork   : 12/5/13 :
: Jane     : 567      : Bowl    : 9/9/13  :
: Jane     : 789      : Plate   : 9/9/13  :
: Jane     : 789      : Spork   : 9/9/13  :
: Bob      : 234      : Plate   : 8/7/13  :
: John     : 345      : Plate   : 4/9/13  :
: John     : 345      : Sponge  : 4/9/13  :

What I want is to get the number of unique contracts for each customer.

my_query
: Customer : Number of Contracts :
: Jane     : 2                   :
: John     : 2                   :
: Bob      : 1                   :

If I wanted just the contracts from June 2013 to June 2014, (past year), then I would get:

my_query
: Customer : Number of Contracts :
: Jane     : 2                   :
: Bob      : 1                   :
: John     : 1                   :

Thanks in advance!

Community
  • 1
  • 1
Ryan B
  • 527
  • 1
  • 6
  • 17

1 Answers1

2

You need to use a subquery to get the distinct customer/contract combinations then you can do a group by on the subquery to get the counts.

SELECT Customer, COUNT(*) AS contract_count
FROM
    (SELECT DISTINCT Customer, Contract
    FROM my_table) contract_data
GROUP BY Customer

If you want to do a date filter, you would filter in the subquery.

SELECT Customer, COUNT(*) AS contract_count
FROM
    (SELECT DISTINCT Customer, Contract
    FROM my_table
    WHERE [Date] BETWEEN #6/1/2013# AND #6/1/2014#) contract_data
GROUP BY Customer
Tmdean
  • 9,108
  • 43
  • 51