count of one thing and count of another ?? LOL

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,223
sorry for the poor title, but I can't think of a simple way to describe it

here are 3 tables that store the history of every transaction made by every user at automated teller machines (atm)

-----------------------------------

table name -- transaction

fields
transaction id -- primary key
user key -- link to user_transaction table
atm key -- link to atm_transaction table
transaction date time -- date time the transaction occurred

-----------------------------------

table name -- user_transaction

fields
user key -- primary key
user id -- user id (duh) , can occur multiple times in this table becasue each user can have multiple transactions

-----------------------------------

table name -- atm_transaction

fields
atm key -- primary key
atm id -- atm id (duh) , can occur multiple times in this table becasue each atm can have multiple transactions

-----------------------------------

these are history tables only

there is a different user table and atm table that stores user name, user address, atm address and so on
we are not interested in those tables

what I want to find out is
the number of atms visited by the number of uniques users

so
20 different atms had 20 unique users
17 different atms had 41 unique users
11 different atms had 3 unique users
1 atm had 57 unique users

--------------------------------------

I don't need the IDs to show up in the final result
just the two columns of numbers

I did this by putting some stuff in excel and doing a pivot table and then making a 2nd pivot table based on the first pivot table
but I know there must be a straight sql way
probably with some transform, partition, over something, something

any ideas ?
--------------------------------------
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
(ms Query or ms Access)

Q1 : get all AMTs and unique users , set query property UNIQUE VALUES = TRUE
Q2 : count users / ATM in Q1
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top