Mysql – need help with this query

MySQL

This will be in MySQL. Table consists of following fields:

Date, ID, blah, blahblah, blahblahblah

I believe the primary key needs to be Date & ID.

The query I want to run is for every Date, count all the IDs but only if this is the last date the ID shows up.

So if the ID shows up in the database on 9/9/16 but NOT on 9/10/16 then we would say that it disappeared and therefore add it to the total count for date 9/9/16.

If that same ID later reappears on 9/11/16, when we run the query again that ID will no longer be added to the count as of 9/9/16. If on 9/12/16 that ID disappears again then it would be added to the count as of 9/11/16.

Here's some sample data. I did not put any data in for the "blahs" since it is not relevant for the query (I believe):

Date,   ID, blah,   blahblah,   blahblahblah
9/8/2016,   1           
9/8/2016,   2           
9/8/2016,   3           
9/8/2016,   4           
9/8/2016,   5           
9/8/2016,   6           
9/8/2016,   7           
9/8/2016,   8           
9/9/2016,   1           
9/9/2016,   2           
9/9/2016,   3           
9/9/2016,   4           
9/9/2016,   5           
9/9/2016,   9           
9/9/2016,   10          
9/9/2016,   11          
9/10/2016,  2           
9/10/2016,  4           
9/10/2016,  6           
9/10/2016,  11          
9/10/2016,  12          
9/10/2016,  13

The expected output run on 9/9/16 would be:

Date, Total IDs Last Day
9/8/16,3
9/9/16,8

The expected output run on 9/10/16 would be:

Date, Total IDs Last Day
9/8/16,2
9/9/16,4
9/10/16,6

Note that when the query is run on 9/10/16, the total for 9/8/16 is different from when it was run on 9/9/16 because an ID (number 6) disappeared but then re-appeared.

Best Answer

One option would be to use a not exists to filter out any rows that appear for a later date.

select date, count(*)
  from your_table a
 where not exists( select 1
                     from your_table b
                    where a.id = b.id
                      and b.date > a.date )
 group by date

Related Solutions

Mysql – SQL Query to get last day of every week

You need to use the DAYOFWEEK function

If the End of the Week in Saturday, any date can be morphed into a Saturday.

For example, this computes the upcoming Saturday

SELECT DATE(NOW() + INTERVAL (7 - DAYOFWEEK(NOW())) DAY);

This computes the upcoming Sunday (you must use WEEKDAY function instead)

SELECT DATE(NOW() + INTERVAL (6 - WEEKDAY(NOW())) DAY);

For a table called mytable with a Date Column called theDate, your summation would be

Saturday

SELECT COUNT(1) DateCount,EndOfWeekDate FROM
(
    SELECT DATE(theDate + INTERVAL (7 - DAYOFWEEK(theDate)) DAY) EndOfWeekDate
    FROM mytable
) A GROUP BY EndOfWeekDate;

Sunday

SELECT COUNT(1) DateCount,EndOfWeekDate FROM
(
    SELECT DATE(theDate + INTERVAL (6 - WEEKDAY(theDate)) DAY) EndOfWeekDate
    FROM mytable
) A GROUP BY EndOfWeekDate;

Give it a Try !!!

CAVEAT if you want any other end of week other than Saturday and Sunday, I wrote an insane algorithm back in Sep 22, 2011 for computing any week starting and ending whatever day.

MySQL join for two queries

YOUR ORIGINAL QUERY

select event_id, count(*) as num_bookmarks from event_connections
where connectiontype = 3 join event_id on events.id=event_connections.event_id group by event_id;

PROPER SYNTAX

select event_id, count(*) as num_bookmarks
from event_connections join events on events.id=event_connections.event_id
where event_connections.connectiontype = 3
group by event_id;

You just had the WHERE in the wrong place

SUGGESTIONS

If all you care about is the event_id and the counts, do this

select event_id, count(*) as num_bookmarks
from event_connections
where connectiontype = 3
group by event_id;

No need to join.

If you want the event title, then you must join

select events.title, count(*) as num_bookmarks
from event_connections join events on events.id=event_connections.event_id
where event_connections.connectiontype = 3
group by events.title;