Skip to main content

Questions tagged [subquery]

SQL term used to describe when a `SELECT` statement is used as part of a larger SQL statement. The larger statement may be DML and is always found within brackets or parenthesis.

Filter by
Sorted by
Tagged with
1 vote
1 answer
30 views

Assistance with MySQL query

I have following tables Tags id | tag_name | slug | 1 | tag1 |tag1 2 | tag1 |tag1 products id | proudct_name 1|product1 2|product2 product_tags id | product_id | tag_id 1|1||1 2|1|2 3|2|1 i ...
scott's user avatar
  • 113
0 votes
0 answers
32 views

MySQL query to reference previous field in subqueries

I have a question on how to best do this. I have a table that contains how users are enrolled in a class, say "manual", "db", "email", etc. (enrolment type). each class has a list of their own class ...
Dave's user avatar
  • 1
0 votes
1 answer
571 views

How to perform query operation on another query result

How to get approved , unapproved , deleted count from the below result using sql query. SELECT approved,deleted_at,count(id)AS count FROM `payment_voucher` WHERE company_id = 1 GROUP BY approved,...
Navaneet Raman Bhaskar's user avatar
1 vote
1 answer
924 views

Distinct result with row_id for limit query

There are 2 tables: CREATE TABLE sample1 ( id int, name varchar, score int, mode int ) CREATE TABLE sample2 ( id int, mode int ... ) I need to select distinct name with MIN(score) and ...
Alex's user avatar
  • 113
1 vote
1 answer
1k views

postgres subquery uses ungrouped column

I have a query in which I am trying to add a column which contains a percentage of records which have a certain value versus the total number of records. I can get the subquery to work on its own, but ...
john doe's user avatar
  • 115
0 votes
2 answers
3k views

Locate multiple duplicate columns in PostgreSQL table

I am trying to report on duplicate records in a single table which has a unique key of app_cao_number. The duplicates occur if either: 1. The Passport field is duplicated; 2. The ID field is ...
Paul Pritchard's user avatar
1 vote
2 answers
616 views

How to get sum of 3 sub count queries and group them using an attribute

I have 3 queries as follows: For the date 2019-12-02, I am calculating 3 type of values. The total value for 2019-12-02 must be the sum of these 3 queries. Q1 SELECT count(*) FROM ...
sadeee nadeee's user avatar
1 vote
2 answers
959 views

Column not like any value in column in other table - function

The two tables in question are: completed_types id | type ---|-------- 1 | XXX 2 | YYY all_types: id | type | sub_type ---|-------|--------- 1 | XXX X | aaa 2 | XXX-Y | bbb 3 | YYY-X | ccc 4 ...
CDickson's user avatar
2 votes
2 answers
1k views

Postgresql expanding view multiple times instead of executing it once

I have a rahter long subquery which I store as a view CREATE VIEW scans_minmax AS SELECT tmp.source, value ->> 'Ok' AS "Ok", ...
Julian's user avatar
  • 123
0 votes
1 answer
462 views

select subquery condition in where statement

I am getting this error: ORA-06550: line 43, column 16: ORA-00904: "STOCK_BALANCE": invalid identifier ...for the following query: select PARTY_ID, PARTY_NAME, IT_ID, IT_ID ||...
Ali Farooq's user avatar
0 votes
1 answer
651 views

Ordering by field, constructed by subquery is slow

My tables (oversimplified for clarity): documents: registrations: |id| |id|document_id|date| My simplified query: SELECT * FROM (SELECT documents.*, (SELECT max(date) ...
Stanislav's user avatar
0 votes
1 answer
3k views

Calculating percentage over sub query SQL

I have a table that records events regarding email campaigns. I want to figure out the percentage of campaigns where there was more than one event happening for the campaign. First I calculated the ...
Patrick Kusebauch's user avatar
0 votes
1 answer
44 views

Query with a few subqueries and appropriate indicies taking, on average, 613ms to execute

I have a ton of queries that do a similar thing as to what I'm doing here, but none of them have the performance issues this one does: SELECT (SELECT lead_id FROM leads WHERE to_id = lead_id OR ...
Adam McGurk's user avatar
-1 votes
1 answer
2k views

Aggregate function on subquery

I have got a SALES table that has 4 document types, and they are created with reference to each other in the following order: Offer -> Order -> Delivery -> Invoice. For each new row their ...
Ching's user avatar
  • 1
2 votes
0 answers
391 views

adding results of InfluxDB subqueries

I'm trying to get the kWh from multiple entities in InfluxDB in Grafana. I have the following raw query. SELECT ("a" + "b") / 3600000 FROM ( SELECT spread("watt_seconds") AS "a" FROM "W" ...
martin blitz's user avatar
8 votes
1 answer
4k views

Replace long GROUP BY list with a subquery

This is a repost of my question on Stack Overflow. They suggested to ask it here: I found an online article from 2005, where the author claims, that many devs use GROUP BY wrong, and that you should ...
André Reichelt's user avatar
1 vote
1 answer
4k views

Sub Query with group by returns more than 1 row Issue

I am facing the issue of subquery returns more than 1 row cause I am using group by in the subquery and outer query both to get the desired result. This is my query SELECT Count(*) ...
Wajid Hussain's user avatar
0 votes
1 answer
80 views

optimize query -- remove nested selects

How can I rewrite the query to increase performance? Here is the query: SELECT flight.*, DAYOFWEEK(flight.flight_time) AS day_of_week, IF(SUM(...
Alexey Burdin's user avatar
2 votes
1 answer
19k views

There is a column named in table but it cannot be referenced from this part of the query

I've been learning Postgres (coming from SQL Server), and this error really confuses me. Here is the code with some sample data: create table T ( ID serial primary key, A varchar(1), B varchar(1), C ...
Chessbrain's user avatar
  • 1,223
0 votes
1 answer
41 views

Need help with correlated subquery

I have two PostgreSQL tables that each contain a column called date_of_incident. They each also have a column called incident_id and this is how the tables are related. I want to update all rows of ...
two4two's user avatar
3 votes
2 answers
204 views

Can subqueries produce different execution plans than CTE's?

I was talking with the bossman, and he prefers subqueries over CTE's. Personally, I loathe subqueries. He mentioned that subqueries can be faster, but I am not convinced. I ran this short test: ...
James's user avatar
  • 2,650
0 votes
2 answers
1k views

Select/print data from subquery

In my application I have a table "user", a table "role", and a table "userroles". Userroles is simple table consisting of user, role, where user corresponds to the id column of the user table. As user ...
paul23's user avatar
  • 483
0 votes
1 answer
2k views

Optimize slow COUNT in MySQL subquery

I have the following query that takes minutes to complete: SELECT DISTINCT Designation, Model, ( SELECT COUNT( Equipment.EquipmentID ) FROM Equipment INNER JOIN EquipmentDesignation_Vw ...
Hajopa's user avatar
  • 1
-2 votes
3 answers
1k views

Can a `Select` Subquery be conditionally returned? [closed]

Is it possible in Postgres to return a column(Resulting from a subquery) only when a condition matches? Query: SELECT (SELECT NOW()) AS FIRST, (SELECT NOW()) AS SECOND Outputs: | first | ...
Nick's user avatar
  • 107
0 votes
1 answer
1k views

Postgres Pass Subquery results to function that returns TABLE

I have a function that accepts arrays as parameters and returns a table. I can get the arguments passed in correctly, but it is returning rows of type record. If I try to expand the record with FROM I ...
Eric Satterwhite's user avatar
1 vote
1 answer
1k views

Is there a way to alias columns for reference in subqueries?

I have a SQL statement in PostgreSQL that takes the following form: SELECT * FROM db WHERE (b + 1) IN (SELECT b FROM db WHERE a = 7) This query returns each record whose b value, incremented by 1, ...
paulinho's user avatar
  • 133
0 votes
1 answer
58 views

How to rank an aggregation in each newly formed group?

The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL. Let's say I have a simplified ERD that looks as follows: This ...
user71346's user avatar
  • 101
-1 votes
2 answers
98 views

How to get column values in where condition in sub query? [closed]

I am trying to get distinct list of values in where condition through sub query but it shows 0 rows in result set. Here is my query. SELECT a.topic_id, t.id as topicid, t.value as value, t.topic as ...
prabhu's user avatar
  • 23
0 votes
1 answer
601 views

How to use query result as REPLACE parameters

i'm trying to: 1- Find a string inside a lot of content 2- Replace that specific part of the string with a new one using REPLACE function like this: REPLACE(column_name, query#1, query#2) Is this ...
gallo2000sv's user avatar
0 votes
1 answer
2k views

Query to get the physical file name of the database

Im trying to get the physical file name of a database, and when i query sys.database_files, under the physical file name column, you get the whole path (ie. S:\Program Files\Microsoft SQL Server\...
Keith Rivera's user avatar
0 votes
0 answers
45 views

Postresql using subquery select in order by clause

Is it possible to order by a selected columnname? SELECT * FROM tablex ORDER BY quote_ident(( select column_name from information_schema.columns where table_name = tablex AND column_name LIKE '...
DataLordDev's user avatar
0 votes
0 answers
1k views

Subquery caching in join

I have very complex MySQL queries that use the same subquery over and over again. I was wondering if MySQL will run the subquery multiple times or use the previous resultset. Is there any way to tell ...
Shahin's user avatar
  • 65
1 vote
1 answer
48 views

selecting data after filtering out

I am using RDBMS MySQL 5.7. I have 3 tables with the following structures: Table A - primary table - products: +----+-----------+-------------+------------+--------+ | id | title | description |...
Shahin's user avatar
  • 65
0 votes
0 answers
35 views

Finding matching sub values?

So not too sure how to go about this, but ideally I want to pull out matching & non-matching info from an array of rows in a table, so I have a table with info like this: 12,2019-07-15 06:02:52,...
Alex's user avatar
  • 1
0 votes
1 answer
199 views

Problem with How to join with subquery

I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get ...
Ugy Astro's user avatar
  • 111
0 votes
1 answer
48 views

Why last table don't using index for joining?

By the help of others members i get an Query idea for my previous problem. But after writing this, the EXPLAIN showing that my last table do't using index for join but the joining ROW is PRIMARY KEY, ...
Rhidoy's user avatar
  • 101
0 votes
1 answer
2k views

Sql subquery for nested parent child data

I have two tables , on parent table (categories ) and child table (features).. I have this query select c.* , (select GROUP_CONCAT(f.featuresId , f.featuresTitle ) from ...
Arash Ammarlooi's user avatar
0 votes
1 answer
39 views

MySQL - Optimize subquery in Select, where and order by

Im running a query like below. SELECT DISTINCT c.`id` , c.`job-cat` , ( SELECT COUNT(*) FROM `jobs` j WHERE j.`mul_category` IN (c.`id`) AND j.`ending` >...
TheDataGuy's user avatar
  • 1,960
0 votes
2 answers
693 views

Postgres select using lateral

I'm after running a query with a subquery (perhaps inefficient) that using a regular expression pattern it can return how many instances of a characters are included with a given record. In doing so, ...
Andrea Moro's user avatar
0 votes
1 answer
1k views

Query to Find Multiple Start Time and End Time on a Single Day based on Boolean Value

I want to find the multiple Start Time and End time in a single Day based on the Boolean value where data is divided into each half hour interval for a day. So Each day is divided into 24hrs/2 = 48. ...
Puskar's user avatar
  • 181
0 votes
1 answer
51 views

How do I create a table with count of instances of an id before an event date that is individual to that id?

I am very new to SQL and have a relatively complex query to write, any help would be appreciated. I have data with the following columns: ORDER_ID CUST_ID ORDER_DATE RETURNED(1/0) I need a table ...
Newb_ql's user avatar
0 votes
1 answer
45 views

Slecting specific query returnned data related records from another table [closed]

Product table: productId | productName | unitPrice | supplierId _______________________________________________ 1 | Iphone8 | 1500 | 3 2 | Nokia 6 | 800 | 1 ...
Aaby's user avatar
  • 37
5 votes
1 answer
10k views

How to take make OFFSET & LIMIT with COUNT (OVER?) when having a subquery?

It appears I have a case I can't quite wrap my brains around. So coming here in hopes to find pointers to a query that maybe could be helpful to someone else too. In the following, I have a query ...
Veksi's user avatar
  • 279
3 votes
2 answers
29k views

Limit left join for multiple rows

Since MySql doesn't support main alias reference in subquery is a bit challenging to create query with multiple rows where you have to limit rows of left join tables. Let's overview simple example. ...
Arsenius's user avatar
  • 133
0 votes
1 answer
47 views

Two aggregated sub queries wont run

I am having trouble with the below, I need to average two columns in a correlated subquery and I cannot get it to work, how do i get both aggregate sub queries to run then go through the outer query? ...
Orfevre's user avatar
  • 35
3 votes
2 answers
75 views

What am I doing wrong with subquery

I cannot work this out, how do I get the subquery to return the lowest course weight for each lecturer, it currently only returns the lowest and is my subquery wrong or outer query? Question: Show ...
Orfevre's user avatar
  • 35
3 votes
1 answer
2k views

Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around?

I have a cursor. Within my loop I select a single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable ...
youcantryreachingme's user avatar
0 votes
1 answer
145 views

Cannot get a user function to co-operate with a subquery in mariadb

The following is a demo of an issue we are having with a function and a subquery. I'm interested to know if it is a bug or if there is a reasonable explaination why it is failing. Notes: Problem ...
ΣΚΟΤ's user avatar
  • 111
2 votes
1 answer
2k views

Having a subquery somehow do the same group by as the outer query

i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance. Basically we'...
magmusik's user avatar
  • 121
2 votes
3 answers
8k views

Return ALL rows based on a matching date also matching a specific value in another column

I'm using SQL Server 2014, exact version: 12.0.5579.0. I've tried a subquery under a SELECT and WHERE statement and it brings back false results with rows of any row with a 1/1/2019 date. It just ...
minnemike's user avatar

1
3 4
5
6 7
15