41440

select columns if happened on same date

Question:

I am trying to craft a query that will select multiple columns (uniqueID, IPaddress, date, country) but I would like the query only to select those records where the same IPaddress and UniqueID showed up on a given day. I have been trying ...

SELECT uniqueID AS uniqueID, IPAddress AS IPAddr, Date AS DDate, Location AS Countries, COUNT(IPAddress) AS IPCount FROM History WHERE (uniqueID= 20677) GROUP BY Date ORDER BY DDate DESC, IPAddr DESC

But it does eliminate records where only one Ip/UniqueID showed up on one day. I really only want to find records where the <strong>uniqueID showed up on the same day with different IPaddresses</strong>.

Answer1:

Not sure if I follow exactly, but sounds like you want to join a subquery:

SELECT a.* FROM History a JOIN ( SELECT uniqueID, Date, COUNT(DISTINCT IPAddress) FROM History GROUP BY uniqueID,Date HAVING COUNT(DISTINCT IPAddress) > 1 )b ON a.uniqueID = b.uniqueID AND a.date = b.date

EDIT: misread your with different IP addresses part, updated.

The subquery will show you all the records that have multiple ip addresses for the same uniqueID/date, then joining that to your history table shows you all the fields for the records you're after.

Answer2:

You need to group by three fields, if you are looking for duplicates amongst them:

SELECT uniqueID, IPAddress, "Date" AS DDate, Location AS Countries, COUNT(IPAddress) AS IPCount FROM History WHERE uniqueID = 20677 GROUP BY uniqueId, IPAddress, "Date" having IPCount > 1;

You dn't really need the uniqueid in the group by clause because you are selecting only one. However, I think it makes the intention of the query clearer.

As written, this will either generate an error (because location is not in the aggregation clause) or return a random value for location, if you are using MySQL). If the latter, try:

SELECT uniqueID, IPAddress, "Date" AS DDate, group_concat(Location) AS Countries, COUNT(IPAddress) AS IPCount FROM History WHERE uniqueID = 20677 GROUP BY uniqueId, IPAddress, "Date" having IPCount > 1;

Also, naming columns after data types and functions is not good idea. In many databases, date is both a data type and a function. Some other name, like DateAdded avoids this conflict.

Recommend

  • What are the reordering columns on laravel-backpack?
  • Android Permission Denial: broadcasting Intent
  • Functional Programming in Swit to distribute array elements to correct “buckets”
  • SQL SELECT ORDER BY multiple columns depending on value of other column
  • How to resolve designated initialization error for UITableViewController?
  • Python list of String to SQL IN parameter
  • format JSON value with a php function and print result inside a div#id
  • Get last call details from Call Log
  • MySQL select genres issue (php)
  • Should I create an object or work with an array?
  • NullPointerException for lwuit.html.CSSEngine.applyStyleToUIElement(Unknown Source)
  • In explicit LINQ-to-SQL (C#) does order matter?
  • Using a join with three tables when a field might be null
  • Bokeh custom ToolTips {safe} tag displays nothing
  • How to translate SQL queries to cypher in the optimal way?
  • Sequelize mssql: order by primary key and limit
  • Sorting table with just jquery, no other plugins
  • Google OAuth2 for an web application hosted behind NAT (intranet server without public IP)
  • inserting items in list in mongodb document
  • How to resolve docker host names (/etc/hosts) in containers
  • Should I be afraid to use UDP to make a client/server broadcast talk?
  • Oracle - Second level subquery cannot see field from main query
  • Best practice to eliminate magic numbers within a member function
  • How to skip require in ruby?
  • C++ Single function pointer for all template instances
  • Most efficient way to move table rows from one table to another
  • Can I have a variable number of URI parameters or key-value pairs in Laravel 4?
  • Add dynamic data to line chart from mysql database with highcharts
  • Using Sax parsing to edit and write XML in VB6
  • Test if a set exists before trying to drop it
  • Cannot resolve symbol 'MyApi'
  • Django: Count of Group Elements
  • Using $this when not in object context
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • embed rChart in Markdown
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Is there any way to bind data to data.frame by some index?
  • How can i traverse a binary tree from right to left in java?