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?
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?
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
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
I’m trying to learn sql query and i want to solve the following problem. Assume the table as follow: id schoolname totalA totalB grade 1 school A 5 5 1 2 school A 5 5 2 3 school B 5 5 1 4 school B 5 5 2 Select schoolname SUM(CASE WHEN (grade=’1′ ) THEN… Read More SQL, sum inside of case condition
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?
I’m wondering if there’s a way I can combine these two queries into one? I need to get the mean and std dev for each column in the company_feature table. I then need to take those two values and use them in an aggregation query on each row in the company_feature table. /* Get mean… Read More Can these two SQL queries be combined?
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
Here is my sqlquery : SELECT * FROM work_orders WHERE LineKey = ‘ABC’ AND StateKey = ‘Approved’ AND orderKey = ‘Released’ AND WOID NOT IN ( SELECT WOID FROM serialized_assembly GROUP BY (WOID) HAVING COUNT(WOID) > 9 ) LIMIT 1 Which does not return any data even though there’s an WOID 1234 that passes all… Read More no Sql data returned even though it exists
I am very new to SQL. I have created a query to pull out the total of purchase invoices we have created and I have Union All to the total of purchase credits we have created. The problem I am having is the credits don’t show a minus in front of the amount. When I… Read More Add negative to credited invoices only