I want to find the number of companies tied to a specific membership field. This is somewhat complex, as many different companies can belong to the same membership number. Companies are identified by a 6 character string (account number), but in this table they can be either 6 or 8 characters long. I am only interested in accounts that are 6 characters, or 8 characters where the last two characters are 00. To make matters worse, there are many duplicate account numbers in this table — the field is not unique.
The membership field is numeric, but can also be null. I care only about non-zero, non-null membership numbers. Unfortunately it’s random whether or not the user input a null, a zero, or an actual number.
The end result is to display all the membership numbers with a count of how many account numbers belong.
All the information is from the same table. Each row will have an account number and membership number among many other fields.
Think of it like a franchise situation. Let’s pretend Burger Joint is our customer, so we have a membership number for them. Then, all the franchisees with different account numbers, use the membership number to do business with us. I’m trying to find out how much REAL business is done with each membership.
I realize this may be a multi-step process. Below is my attempt to try to get back the correct account number types. AQT didn’t seem to like it though.
where (length(account) > 6 and substring(account, (length(account) -2), length(account)) ='00')
Advertisement
Answer
Companies are identified by a 6 character string (account number), but in this table they can be either 6 or 8 characters long. I am only interested in accounts that are 6 characters, or 8 characters where the last two characters are 00
WHERE (accountnumber LIKE '______' or accountnumber LIKE '______00')
I care only about non-zero, non-null membership numbers
AND membershipnumber > 0
You don’t need to specifically check for null member numbers; requiring the number be greater than 0 excludes nulls implicitly
The end result is to display all the membership numbers with a count of how many account numbers belong
SELECT membershipnumber, COUNT(DISTINCT accountnumber) as count_unique_accs FROM ... WHERE ... GROUP BY membershipnumber
Doesn’t matter if accountnumber is duplicated: count distinct only gives the unique occurrences. If 123456 and 12345600 are the same accountnumber, substring inside the count
Edit:
By this last sentence I mean:
COUNT(DISTINCT SUBSTRING(accountnumber, 1, 6)) --sql server syntax