Need help with comparing rows within a table

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything

Need help with comparing rows within a table

I have a table where there can exist rows with duplicate acct_numbers, sales_locations, and sales_dates, BUT have DIFFERENT customer_names.  I want to be able to create a way to identify each of these rows as a “1”, meaning they are separate transactions but have these three attributes matching, so I can count the occurrence of customers who subscribed on the same account, at the same location, on the same day.

 

I’m not too advanced with SQL…any suggestions on how to do this?  Thanks.

2 REPLIES
rjg
Supporter

Re: Need help with comparing rows within a table

Something like this will get you what you describe.

 

SyntaxEditor Code Snippet

 Sel cname,acct_num,str_loc,sl_dt,1 from custs where (acct_num,str_loc,sl_dt) in (sel  acct_num,str_loc,sl_dt 
 from(sel acct_num,str_loc,sl_dt,count(*) cnt from custs
group by 1,2,3 having cnt > 1)c(acct_num,str_loc,sl_dt,cnt)
 ) order by 2,3,4

Rglass 

Re: Need help with comparing rows within a table

Rglass, this appears to work! Thanks!  However, I forgot one other validation that needs to occur. 

 

Each row also has a field called TREATMENT (with a Y/N value).  In addition to the three matches already mentioned, I only want to apply the "counter" (i.e. 1) when the combination of TREATMENT for these rows are either "Y" and "Y" or "Y" and "N"...I don't want to count the rows when the two columns both have a TREATMENT value of "N".

 

This is assuming that there are only two rows that have the 3 matches mentioned above (which I believe is the majority of the file)...there are some "acct_num, str_loc,sl_dt" combinations that could have up to 4 rows.  If at least one of the rows has a "Y" value then I want all 4 rows to be counted/flagged.

 

Does this make sense?  Thanks again for your time and help!