I wrote the below query with a CTE attempting to return all the dates on which daily transactions exceeded the average of all daily transactions; however, MySQL keeps returning an error message for something around line 1. I've attached a…
I have the following query: WITH matching_data as ( select * from consultants_matching_data_for_project(6001) ) select id, matching_data.city_matching from consultant_profiles LEFT OUTER JOIN matching_data on matching_data.consultant_profile_id = consultant_profiles.id; Is there any other way to join with matching_data result without using a…
I am trying to write a query where I select records by some condition, update one column of those records and then return them, all in one transaction. I have tried two options. First with CTE: BEGIN; WITH unwatched_rows AS…
sorry if this is so obviously easy but I am new to this! I am trying to add another WITH statement to a query that works perfectly fine, but breaks when I edit it. The query I am trying is:…
I'm using Laravel package staudenmeir/laravel-cte but am not able to articulate the query I want MY SQL query : WITH T AS ( SELECT Id, DeviceType, DeviceId, ENERGY_Total, `Time` FROM devices_sensor_data WHERE `Time` BETWEEN '2023-03-28 00:00:00' AND '2023-03-27 20:40:00' AND…
I am trying to find a way to delete all rows with NULL values in a table. On this post, I saw that you can't specify a target table from updates in the FROM clause. Therefore, I tried building a…
I built a dynamic query that returns Total count of entries in CTE Paginated entries from CTE List of column names used in CTE Requested page size and page number The beauty of in returning total count + pagination in…
I have created my first MySQL query to get the minimum LTP by MarketName/SelectionName. It works ok but I'm struggling to work out how to get the rest of the fields from the original table (checktable). I just want the…
I have two tables with many to many relationship in Postresql database. The first Topic table consists of three columns. Their names are id, name and parent. Topic table has hierarchial structure: id name parent 1 Mathematics 0 2 Algebra…
I am curious what techniques Database Developers and Architects use to create dynamic filter data response Stored Procedures (or Functions) for large-scale databases. For example, let's take a database with millions of people in it, and we want to provide…