How to aggregate event dates based on aggregated table?

I have a table that aggregates disables/reenables events in the following format: Disabled Date Enabled Date Count 01/01 01/01 5 01/01 02/01 2 03/01 05/01 1 04/01 05/01 5 and want to build a report that aggregates the number of disables and reenables per day: Date Enables Disables 01/01 5 7 02/01 2 0 03/01… Read More How to aggregate event dates based on aggregated table?

How to subtract date-time from current date-time in SQL?

I have date-time field in my database like that 2023-01-18 05:43:18 and I want to subtract this field from the current date and time using and sql query to get result when the difference from this field less than one minute . >Solution : Use TIMESTAMPDIFF to calculate the difference: TIMESTAMPDIFF(MINUTE, localtimestamp, columnname) AS difference… Read More How to subtract date-time from current date-time in SQL?

SQL count number partition by id and displaying only top 3 of each id

question Hi everyone, i tried attempting this SQL question but am facing difficulty in this question. particularly counting the number of deals and summing the total deals per merchant. and then i also do not know how to display only the first 3 entries as well. should this problem be resolved via the partition by… Read More SQL count number partition by id and displaying only top 3 of each id

MySQL sub select ignores condition

This query works good but ignores the AND members.active=’1′ condition completely. It delivers always the same results, no matter if active is 1 or 0. Any idea? select amount, count(*) AS quantity from ( SELECT participations.member_number, COUNT(participations.member_number) AS amount FROM participations LEFT JOIN members ON participations.member_number=members.member_number AND members.active=’1′ GROUP BY participations.member_number ) DT group by… Read More MySQL sub select ignores condition

How to remove specific number in string that is in random order?

Lets say that I have data rows that look like this: +—————————-+ + test_table + +—-+——–+————–+ + id + word + updated_word + +—-+——–+————–+ + 1 + g00gle + ggle + + 2 + bread0 + bread + + 3 + 0bject + bject + + 4 + d0d0 + dd + +————-+————–+ What statement… Read More How to remove specific number in string that is in random order?

Use Indexes For Join on Indexed DATETIME and Indexed DATE columns

I have three tables a, b, and c in my MySQL 5.7 database. SHOW CREATE statements for each table are: CREATE TABLE `a` ( `id` int(11) NOT NULL AUTO_INCREMENT, `DT` date DEFAULT NULL, `USER` int(11) DEFAULT NULL, `COMMENT_SENTIMENT` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a_DT_USER_IDX` (`DT`,`USER`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `b` (… Read More Use Indexes For Join on Indexed DATETIME and Indexed DATE columns