How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask ATLPROG Your Own Question

ATLPROG
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
44910485
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

I have the following guest information in a table: RRN Col1

Customer Question

I have the following guest information in a table:
RRN Col1 Col2 Col3
1 SMITH/M ***@******.*** ID1
2 SMITH/M ***@******.***
3 MEYERS/A ID2
4 MEYERS/B ***@******.***
5 SMITH/S ***@******.*** ID1
Rows 1,2,5 represent one household with different data in 3 columns that identifies them as a single household. Rows 3,4 represent another household with different data in 3 columns that identifies them as a unique househould.
I need to find a way to count the number of households with unique entries in either of the 3 columns.
So, the result of the SQL operation would be 2.
Submitted: 1 year ago.
Category: Programming
Expert:  Aitizaz replied 1 year ago.

Hello and welcome to Just Answer! i am Aitizaz and i will assist you today

can you please put this information in a text file with formatting so that i can have a clear understanding of the requirements?

Customer: replied 1 year ago.
I just posted it. Thx.
Expert:  Aitizaz replied 1 year ago.

OK! let me have a look at it

Customer: replied 1 year ago.
No worries. I can explain in more detail why the columns can have different information but relate to the same household.
Expert:  Aitizaz replied 1 year ago.

What is the condition to identify different household?

Customer: replied 1 year ago.
Unique information in any column. For example, rows 1, 2 and 5 are the same household, because 1 of the 3 columns match for those 3 rows.
Expert:  Aitizaz replied 1 year ago.

when do you need this?

Customer: replied 1 year ago.
What are my options? It would be great to be able to work on the rest of it this weekend. Is it possible to have tomorrow?
I'm in Billings, Montana, USA.
Expert:  Aitizaz replied 1 year ago.

in fact i have a commitment this weekend. i will try to do it as early as possible. can we take it to the next week if i cannot complete that on weekend?

Customer: replied 1 year ago.
As long as it would be early next week if you can't get to it before hand. I'm using command line SQL so the solution needs to be a single statement, not an SQL procedure. Thx!
Expert:  Aitizaz replied 1 year ago.

Yes i will try to do that as early as possible and as a single statement.Thanks

Customer: replied 1 year ago.
Aitizaz, any progress on the SQL statement?
Expert:  Aitizaz replied 1 year ago.

i remained busy on the weekend as told you earlier. i will start working on it now and will get back to you soon.

Expert:  Aitizaz replied 1 year ago.

Don't you think the result of the query should be 3?

Expert:  Aitizaz replied 1 year ago.

Since MEYERS/A and MEYERS/B are two different records with nothing in common in the rest of the two columns as well

Customer: replied 1 year ago.
You are correct. I'm sorry -- I gave you bad info. The accurate result is 3. What I was trying to show is that there are three columns that have to be checked and that any of the 3 columns could be used to identify a household. For example, in Row 5, if the Col2 entry was***@******.*** instead of***@******.***, it would match with Row 2 on one entry, thus count as one household. Again, sorry for the confusion in my data example.jef
Expert:  Aitizaz replied 1 year ago.

No problem. i am working on it and as soon as i am successful, i will post the SQL query here.

Expert:  Aitizaz replied 1 year ago.

can you send me the complete database ( or some portion of it) so that i may apply the query on it?

Customer: replied 1 year ago.
Aitizaz, not easily. It is on my mainframe, not my PC. Please send me what you have and I'll run it quickly across my guest data set.
Expert:  Aitizaz replied 1 year ago.

i am trying different queries and have not been able to get the exact result as yet. but i am working and will intimate you shortly

Customer: replied 1 year ago.
Aitizaz, are you able to develop a solution?
Expert:  Aitizaz replied 1 year ago.

I am out of ideas on this. i will opt out so that any other expert may help you. Thank you very much for your patience.

Customer: replied 1 year ago.
Will somebody else pick this up automatically or do I submit the question again?
Expert:  Jason Carlamere replied 11 months ago.

Hello - My name is***** see that our previous expert was unable to help you. I will pick up this up and assist you. I see that you are running this in a mainframe - Is this a going against a VSAM file or are you running a query against a database. I think I understand your requirements, and you are looking for a SQL query correct ?

And just so I am clear on my understanding - your example data would return to you 5 correct ? All rows are unique.

Also - what type of database are you running this against ?

Customer: replied 11 months ago.
Thanks Jason. Let me know if you have any questions about the SQL statement I'm trying to get. I've asked that it be a single pass SQL statement so it can be executed/run via command line. But, if that is not going to work, I could be open to multi-pass statement where pre-steps build a temp file.
Customer: replied 11 months ago.
PS: Cute picture ... which one is you? Ha!
Expert:  Jason Carlamere replied 11 months ago.

I'm the cute one .. :).. I have a question. To help me understand you goal. You say in your initial post that the result should be a count of 2 but I see all 5 rows have unique values when comparing all 3 columns.

Expert:  Jason Carlamere replied 11 months ago.

Are you basing unique if col1 or col2 or col3 are unique.

Walk me through your though process of why 1,2,5 is counted as 1 household.

Wouldn't SMITH/M *****@******.*** ID1 be unique from SMITH/M *****@******.*** and SMITH/S *****@******.*** ID1 ?

Customer: replied 11 months ago.
The database I'm using is IBM DB2 (IBM i to be specific). Yes, I'm trying to count guest households where col1, 2 or 3 is unique. To provide a little more clarity, Col3 is actually our loyalty card number which may or may not be present depending on when the user buys it. And, what you are seeing is not actual data from our database, it is made up to try to simply represent the data layout where Col1 is our guest name, Col2 is an email address and Col 3 is the loyalty card number.This is a database of transactions where our guests supply the information, meaning I'm dealing with user input and they don't care if they supply easy information for me to count households. So, let's look at records 1, 2 and 5 and why they are the same household.Row 1 and 2 have the same Col1 entry (Name) so they come from the same household -- even though at some point SMITH/M changed his email address from aol to yahoo.com. And at the transaction time of Row 1, SMITH/M had a loyalty card (Col3), but when he stayed with us later, represented by Row 2, he did not have his loyalty card.Row 5 is a transaction where SMITH/M's wife was the main name on the transaction (SMITH/S) -- she even used the household's loyalty card number to tie it in, but she used her old aol account when staying.That is our challenge in SQL: using any of the three columns as unique identifiers to count the number of households in our database. We currently have custom code (not SQL) to do the work, and I'm trying to simplify our ad-hoc reporting of households down to a SQL statement my team can run anytime marketing wants to know.Hope that helps.
Expert:  Jason Carlamere replied 11 months ago.

Got it - I have your sample data loaded up in a MySQL database and will get back to you. If I can't come up with anything shortly I will release the question to see if another expert can assist you.

You have a complex problem and I don't know if a SQL query can give you what you need. You will for sure be able to accomplish this with a stored procedure that exists in the database. A stored procedure will give you the ability to perform logic against the data where SQL only allows you to pull data.

I will though try my best to see if a query can be written for this.

Customer: replied 11 months ago.
Yep, a stored proc will do the job. I've been batting this around for a long time and trying to see if an outside perspective would find a solution for the command line/ad-hoc need I have. Thanks for trying!
Expert:  Ronel Greaves replied 11 months ago.

Good morning, do you still need help with this?

If so, I should be able to assist.

Expert:  Ronel Greaves replied 11 months ago.

I would like to begin by asking -- When you look at the data, how are you personally able to recognize that several rows have the same household?

Once we know that, we should be able to build a query around it.

Customer: replied 11 months ago.
Ron, thanks, ***** ***** point Jason has taken over the assignment.
jef

Related Programming Questions