MYSQL query to group by only if not a specific date

Question

I have a function that tracks clicks. it groups them by day so the user only gets credit for one click per day.

The client wants it so on one special day the user gets credit for all clicks on that day.

My initial idea is to have two functions. the first function pulls all the clicks that do NOT equal the special date and group them by day. The second functions pulls only clicks from the special date. Then I add the two together.

Is there a way to write the query so I don’t have to use two functions?

my current query to pull all clicks that are NOT on the given date is:

$sql = "SELECT DATE(timeclicked) AS Date, platform, COUNT(DISTINCT email) AS click_sum 
FROM trackclicks 
WHERE email='".$email."'
AND uid='".$uid."' 
AND action LIKE '%completed%'
AND date(timeclicked) !=  CAST('".$giving_date."' AS DATE)
GROUP BY platform, DATE(timeclicked) ";

ADDITION

this is in addition to my last question. The query worked and displays this in PHPMyAdmin

Date          platform      click_sum
2015-10-03    Facebook      13
2015-10-04    Facebook      1
2015-10-03    Twitter       14
2015-10-04    Twitter       1

my query ends with this:

if ($result = $mysqli->query($sql)) {
    $response['sum'] = $result->num_rows;
}

which is giving me 2 and I need it to give me 14 for facebook and 15 for twitter

if you want it to sum the platforms, then use GROUP by platform at the end of the query. But this will screw up the counts. You can probably accomplish the funky logic of allowing non-distinct counts on that one specific date in a subquery, which will allow you to then group your results. It only worked when you were grouping by date and doing the conditional logic for a date, now that you want to group by platform and combine dates I don't think it will work that way, need a subquery. — RightClick
– RightClick, Commented Oct 5, 2015 at 19:58

RightClick · Accepted Answer · 2015-10-05 18:59:22Z

1

This is just me adding an if into your statement. Can't test because I don't have the database setup. Hope it works for you. The if just checks and uses distinct count except for on that special date.

$sql = "SELECT DATE(timeclicked) AS Date, platform, IF(date(timeclicked) !=  CAST('".$giving_date."' AS DATE), COUNT(DISTINCT email), COUNT(email)) AS click_sum 
FROM trackclicks 
WHERE email='".$email."'
AND uid='".$uid."' 
AND action LIKE '%completed%'";

answered Oct 5, 2015 at 18:59

RightClick

1,1277 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Jason Over a year ago

I revised my post. I forgot the add the GROUP BY. I need to run the if statement on the GROUP BY

RightClick Over a year ago

$sql = "SELECT DATE(timeclicked) AS Date, platform, IF(date(timeclicked) !=  CAST('".$giving_date."' AS DATE), COUNT(DISTINCT email), COUNT(email)) AS click_sum  FROM trackclicks  WHERE email='".$email."' AND uid='".$uid."'  AND action LIKE '%completed%' GROUP BY platform, DATE(timeclicked) ";

RightClick Over a year ago

I don't think you do. You still want the count for each day right? Just not a distinct count on that one day? I'm honestly not sure what to tell you without seeing the table...but I'm pretty sure IF is a good start when you have 1 case that needs a different total.

RightClick Over a year ago

you can learn more about if here dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html

Jason Over a year ago

I am losing it! you were correct, here is what the query gave me. which is correct BUT now I can not get the results to display properly. They are giving me 2 instead of adding together the 2 rows per platform.

2015-10-03    Facebook      13 2015-10-04    Facebook      1 2015-10-03    Twitter           1 2015-10-04    Twitter           1

|

Collectives™ on Stack Overflow

MYSQL query to group by only if not a specific date

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related