I am using using MySQL (InnoDB engine). I am running SQL query with WHERE IN statement which is also ordered. I decided to create the perfect index, so my query will not run slow. Surprisingly, after creating the perfect index…
I have the following data: sale_id sale_date installments total_value 1 2023/01/01 2 100.0 1 2023/02/01 2 0.0 1 2023/03/01 2 0.0 1 2023/04/01 3 90.0 1 2023/05/01 3 0.0 1 2023/06/01 3 0.0 2 2023/01/01 1 100.0 I need to…
I created the following table: CREATE TABLE IF NOT EXISTS public.publications ( publication_id bigint NOT NULL DEFAULT nextval('eap_publications_publication_id_seq'::regclass), title character varying(1000) COLLATE pg_catalog."default" NOT NULL, author character varying(1000) COLLATE pg_catalog."default" NOT NULL, type integer[] NOT NULL, sys_publication_timestamp timestamp without time…
i have one mother table Person and 3 daughter tables : EMPLOYEE, PROSPECT, VISITOR. their is a field name PER_CATEG that contain the category of the person for each row ("EMP" for employee, "PROSP" for prospect and "VIS" for visitor).…
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:…
ITrying to get 2 separate select statements to show in one result set using a user defined variable. Should I use a Union or With somewhere here? SET @X1 = ((SELECT SUM(ef.Fee * (rd.ActualReturn - ra.startdate)) as "total revenue from…
I have a table users (username, email, validts, confirmed. I want add 1 billion unique rows I tried to do this but it only adds 16 unique values INSERT INTO users (username, email, validts, confirmed) SELECT CONCAT('user', n) AS username,…
I have a complex query. The following below is the most basic example of what I want to do. SELECT match_by_color AS match_type, t1.* FROM t1 WHERE ( t1.color LIKE 'color%') -- match_by_color OR (t1.type in (select type from t2))…
Lets say I have a table of: order_id, agent_name, order_amount. order_id agent_name order_amount 1 Justin 500 2 Justin 500 3 Justin 1000 4 Justin 1000 5 Pablo 750 6 Dinesh 500 How would I write a query to apply DISTINCT…
I would like to add some SUMs from subqueries in the SELECT into another new field. Here is the query that I am currently using: SELECT DAYNAME(date) AS day, DATE_FORMAT(date, "%d-%m-%Y") AS date, (SELECT SUM(qty) AS product1_shift1 FROM bill_details JOIN…