Jump to content
nate123

SQL query to count unique IDs that meet criteria in 2 columns where criteria are not always present on same row

Recommended Posts

I have a conundrum i'm trying to figure out and hoping for some help.  In my simplified example below, I am looking for a query that will distinctly count the number of IDs in column 1 based on the criteria that City = Chicago and Existing Customer = Yes.  The problem is that there are some IDs that meet both criteria, but not on the same record.  

I can write a query that gives me a count of 1 (the ID that has both criteria on the same record), but I need a query that will give me a count of 2.  Is this possible in SQL?  Any guidance is appreciated.  Thanks!

ID City Existing Customer
123456 Chicago  
123456   Yes
789012 Chicago Yes

Share this post


Link to post
Share on other sites

I suppose you could write a query that would work by concatenating multiple rows, but that's only going to work if each row only has data in a single column.  Otherwise, it's not the best design to store data like that.

Share this post


Link to post
Share on other sites

Thanks for the input.  I agree it's not an optimal way to store data.  My example may not have been great.  The actual dataset is timestamped event data (think transaction logs), so it's not uncommon for some events for a given customer to have certain columns be null, and present on other events.  I'm thinking this just might not be feasible with the dataset I have (which has lots of columns).

Share this post


Link to post
Share on other sites

Yeah if there's a lot of processing that needs to be done then it might be best to get all of the records and then process them with another language before outputting everything.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...