I have 2 tables companies and invoices I want to select all companies with their most recent invoice price. I don’t seem to get it working. This is what I tried: But the query loads very long and I don’t know why. The structure looks like this: companies invoices The BC_ID is the same as the compa…
Tag: mysql
Overlap the intervals using mysql
+——+————+————+ | id | start_date | end_date | +——+————+————+ | 1 | 2019-01-01 | 2019-01-12 | | 1 | 2019-01-10 | 2019-01-27 | | …
SELECT with a variable
I have 3 tables : t_object : With all the classes t_operation : With all the operations, link with t_object.Object_ID = t_operation.Object_ID t_operationtag : With all the tag value for each operation, link with t_operation.OperationID = t_operationtag.ElementID I try to get an list for each of my operation (…
calculating percentage on multiple sql tables
There are 2 tables mobile and web Table name: Mobile Table name: web I need to calculate percentage of users who only visited : mobile, web and both. That is, the percentage of users who are only in the mobile table, only in the web table and in both tables. The sum should be 1. I tried using union but
How to Find People Who did Transaction Twice on The Same Day and Who did Transaction Consecutively in Less than 10 Minutes
I have another table which looked like this: I need to write two queries: Find users who did transactions twice on the same day, what product were they bought and how much were they spend on the 1st transaction. Find which users did transaction consecutively less than 10 minutes! I am very appreciating for yo…
Why I get error group by result of subquery?
I want to find the number of trace records which has same telephone,project_id,create_time,trace_content But this sql: returns wrong result. If I use: returns correct result. Mysql version is 5.6.26 Could anyone tell me the reason？ For example the table contains these data: id = 1, project_id = 1, telephone =…
Selecting all entries in a table before this month
I’ve been trying different solutions but none seem to return the same values as I get from excel. I want to return all the items from a table where the entries in a column are before the current month value. So, if this month is January 2020, return the values before January 2020. Ideally it’s goi…
How to tell MariaDB/MySQL that a column is unique due to HAVING clause
With ONLY_FULL_GROUP_BY set (which I don’t want to/can’t change) the following query errors with ERROR 1055 (42000): ‘dbname.test1.person_id’ isn’t in GROUP BY (or equivalent longer message in mariadb). How can I “tell” MySQL that I know the person_id is unambiguous b…
Order a MySQL query by fastest response time
I have a SQL query: SELECT number, ResponseTime, TicketCreateTime, round(time_to_sec(timediff(ResponseTime, TicketCreateTime))/60,2) AS FRMins FROM (SELECT TE.id, T.number, T.ticket_id, TE….
Group numbers by 45 in mysql
I have a database in which I store wind direction readings by degrees. I need to do two things really, i need to group all readings by 45 (basically 45 degrees between “N”, “NE”, “E”, “SE”, “S”,”SW”,”W” and “NW”) a…