Скачать или смотреть How to Filter SQL Data Based on Multiple Columns in a Table

Learn how to effectively filter SQL data using multiple columns with this comprehensive guide. Perfect for analysts and developers dealing with location-based data.
---
This video is based on the question https://stackoverflow.com/q/71405703/ asked by the user 'pquest' ( https://stackoverflow.com/u/10796309/ ) and on the answer https://stackoverflow.com/a/71407188/ provided by the user 'Anthony Norwood' ( https://stackoverflow.com/u/18408939/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Filter data in SQL based on multiple column in a table

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
A Guide to Filtering SQL Data by Multiple Columns

When working with SQL database tables, especially those containing location data, you may need to filter records based on specific criteria. In this guide, we will discuss how to filter records from a table containing geographic locations of delivery agents, focusing specifically on those who have multiple entries for the same day. We'll explore this in the context of a sample table and provide a detailed step-by-step explanation of the SQL code to achieve the desired results.

Understanding the Data

Let’s consider a sample table named geo_loc that tracks the latitude and longitude of delivery agents along with timestamps:

[[See Video to Reveal this Text or Code Snippet]]

The objective is to filter out agents who have more than one unique GPS entry (combination of latitude and longitude) for a given day. For example:

Agent 1134 has multiple GPS entries and should be included in the filter.

Agent 0908 has the same GPS coordinates throughout the day and should be excluded.

To achieve this output, we need to follow a systematic approach.

Steps to Filter the Data

Step 1: Convert the Date Format

First, we need to convert the date column to isolate the date without the time. This can be done using the SQL CONVERT function:

[[See Video to Reveal this Text or Code Snippet]]

This provides a cleaner date format, which is essential for grouping entries.

Step 2: Group the Entries

Next, we will group the data by agent_id and the converted date. We will also create a concatenated column for latitude and longitude. This helps ensure that we get only unique combinations:

[[See Video to Reveal this Text or Code Snippet]]

Then we can apply COUNT(DISTINCT ...) to count the unique lat/long combinations:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Derive Filtered Data

Using the above aggregation, we will know the count of unique GPS entries per agent on a specific day. Here’s the SQL query that combines these elements:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Use a Common Table Expression (CTE)

To streamline the filtering process, we can encapsulate our previous query in a Common Table Expression (CTE) like so:

[[See Video to Reveal this Text or Code Snippet]]

Step 5: Final Selection of Data

Finally, we can select all relevant records from the original geo_loc table based on our unique counts:

[[See Video to Reveal this Text or Code Snippet]]

Putting It All Together

The complete SQL script will look like this:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By following the aforementioned steps, you should now have a comprehensive understanding of how to filter data in SQL based on multiple columns. This technique is especially useful for analyzing location-based records and can be adapted for various use cases beyond just delivery agents.

Feel free to experiment with the queries shared here and adapt them to suit your specific data needs!