MySQL Multiple Conditions in Grouped Data-CodePudding

I am trying to see how many users have clicked on an ad campaign and signed up afterwards.

My Logging table has the columns IP, UserID, and Data (this has the $_GET info like utm_source = newsletter). A user may click a link in newsletter and then later sign up on my website (which is indicated when they have a UserID that is not blank). I am trying to group by IP and then see if any IP has had an instance of utm in Data column and a non-blank UserID (but note that these 2 criteria may be true on different rows)

Currently I have:

SELECT `ip`, GROUP_CONCAT(DISTINCT(`UserID`)) FROM `Logs`
WHERE `Data` LIKE '%utm%'
GROUP BY `ip`

However this is not correct because the WHERE statement filters entire rows, not within the grouped data.

Let me clarify this further.

If table looks like

IP              UserID     Data
12.34.56.78     abcde      
12.34.56.78                utm_source = email
87.65.43.21     xxxxx
19.28.11.11     random
19.28.11.11     random
19.28.11.11                utm_source = newsletter

I want the output to look like

IP              UserID     Data
12.34.56.78     abcde      utm_source = email
19.28.11.11     random     utm_source = newsletter

CodePudding user response：

Add a HAVING clause, which treats similar to a WHERE condition for an aggregation, principally in order to provide getting a concatenated non-null Data and UserID values at the same time per each ip value along with individually using GROUP_CONCAT() function such as

SELECT `ip`, 
       GROUP_CONCAT(DISTINCT 
                        CASE
                        WHEN `Data` LIKE '%utm%'
                        THEN
                             `Data`
                         END
                      ) AS Datas,
       GROUP_CONCAT(DISTINCT 
                             `UserID`
                      ) AS UserIDs
  FROM Logs
 GROUP BY `ip`
HAVING MAX(`UserID`) IS NOT NULL AND MAX(`Data`) IS NOT NULL

Demo