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.
716 questions
0
votes
1
answer
783
views
Using alias in Group by
I am running into a problem where I have to use an alias (Ordernum) in a group by. I have found that this could be done somehow with subquery but I don't have any success with that and also those are ...
- 1
0
votes
2
answers
73
views
Independent aggregation of related entities, while also sorting by related (in a single statement)
I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named ...
- 101
1
vote
1
answer
1k
views
Why is this postgres query ignoring the index in the subquery?
I have a couple of tables, companies and datapoints. If I query for datapoints from a given company, it uses the index:
sophia=> explain analyze select count(distinct(source_doc)) from public....
- 133
4
votes
3
answers
6k
views
MySQL reuse select aliases
I currently have a query where I'm doing two subqueries to get X, Y data:
SELECT
t.series AS week,
( ... ) X,
( ..., AND ... ) Y,
ROUND(( ... ) * 100) / ( ..., AND ... ), 2) Z
FROM series_tmp ...
3
votes
1
answer
81
views
Query optimisation
I want to optimize 2 queries from 2 tables(orders, actions) with relation one to many
Now I make one select from orders:
SELECT "id"
FROM "orders"
WHERE "orders"."...
- 142
2
votes
0
answers
628
views
Correlated subquery with common table expressions in SQLite
Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
- 131
-1
votes
1
answer
784
views
MYSQL Error: Incorrect usage of UNION and ORDER BY?
Am trying to randomize my first query but ran into an error "Incorrect usage of UNION and ORDER BY" Kindly find below my queries:
SELECT *
FROM(
SELECT deal_page.id,
...
- 19
0
votes
2
answers
472
views
MySQL subquery with WHERE filters not working as expected
I have a question/tagging system like StackExchange. I want to display the tags associated with each question and also show the total number of times the tag is used, unless the question has been ...
- 101
1
vote
2
answers
537
views
MySQL - GROUP BY not working with multiple CASE WHEN statements
I am working on a database with Kickstarter database, and I'm trying to:
Define short, medium-lengthed, and long campaigns
See how much each length of campaign raise
Convert different currencies into ...
- 11
1
vote
0
answers
37
views
subquery returns more tha one row
I want to update data of a table where data-name matches with another table and also one data are common with many items.
For example, I have a cycle in which tyre is a part and quantity of tyre is ...
- 11
1
vote
1
answer
1k
views
how to replace with default value when querying a JSONB column with a key that is not present in a particular row giving empty result
I have a table that has a jsonb column as 'result' which has an array of objects.
Looks like this for every row:
[{"grade": "A", "subject": "MATH"}, {"...
- 11
0
votes
1
answer
27
views
Return average using same table data
I've got this query I'm testing with:
select sum(units) / (select count(distinct week) from record_sales_data where customer_country_id=2 group by customer_country_id) as average
from ...
- 141
0
votes
1
answer
17
views
Query finds no values when sharppend
I am trying to join two datasets and find the most recent certification date
I the following 2 datasets
Certifications
(1, 'Judge Level 1', 'Judge', 10, 2),
(2, 'Judge Level 2', 'Judge', 20, 2),
(3, '...
- 103
7
votes
2
answers
10k
views
Does Postgres optimize this JOIN with subquery?
In Postgres 12, I have a table purchase_orders and one for its items. I'm running a query that returns PO's for a given shop and a sum of items ordered on each PO:
SELECT po.id,
SUM(...
- 73
3
votes
2
answers
7k
views
how to sum values from subquerys
i need to collect some values from multiple tables and sum this values as a column to the result table.
my query looks like this:
SELECT
u.ID as id,
(
SELECT `meta_value` as user_name FROM `...
- 133
1
vote
0
answers
20
views
Why did Codd, et al. prohibit intermediate scalars in SELECT - and how would they have recommended avoiding syntactic repetition?
(I'm asking this because I was reviewing this older question I asked and because I'm currently writing some queries and getting annoyed at how much I need to repeat myself).
In ISO-compliant SQL ...
- 603
0
votes
2
answers
91
views
T-SQL: how to show the corresponding column's row to an aggregating function's result?
Everyone,
I have a question - how to show the (possibly ambiguous) column that corresponds to the value retrieved by an aggregating function?
Related to the dataset below, the task is to find the ...
- 151
0
votes
2
answers
10k
views
Selecting distinct rows from two tables, using multiple conditions
I'm trying to figure out a query. I have an offices and meetings table in my DB. Offices can have multiple meetings (office ID is a FK to the offices table). Some meetings allow lunch, some do not. ...
- 113
1
vote
1
answer
38
views
How to JOIN a table with itself while using a sub-query?
I JOIN a table to itself as
SELECT t1.TagID AS TagID1
,t2.TagID AS TagID2
,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY ...
- 4,531
1
vote
1
answer
995
views
T-SQL query - how to AVG() the SUM() of columns' rows to a new row column?
What is asked is to find the average marks for each student comprised by the total of the marks divided by their count.
How should the query look like to achieve this, since the AVG function is an ...
- 151
0
votes
1
answer
186
views
How to split a SUM column based on another BIT column? [duplicate]
MySQL v5.7
Having this table:
id | sprId | load | calc
-----|----------|---------|---------
1 | 1 | 1 | 7
2 | 2 | 1 | 1
3 | 2 | 1 | ...
- 115
1
vote
1
answer
8k
views
Subqueries for JSON path where outer query is part of a UNION result in strings rather than JSON
Bear with me - the example code has bit going on and I'll do my best to explain it, below.
with ENTITIES as (
select [name] as entityName from (values ('A'), ('B'), ('C')) X([name])
),
PROPERTIES as (
...
- 1,645
3
votes
1
answer
20k
views
ERROR: subquery must return only one column
I have two tables one is Category and other one is Product.
Table Description are
Category
name
description
parent_id (self referencing key) (can be upto 3 levels)
Product
name
description
type
...
- 133
1
vote
1
answer
3k
views
Converting rows to columns using crosstab in PostgreSQL not working (relation "table" does not exist)
I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query.
with checked_adgroup AS (
...
0
votes
1
answer
101
views
Optimize query with a subquery (index is not used)
I have following query:
SELECT
`status`, `is_client_coordinator`, id, first_name, last_name
FROM `users` `t`
WHERE t.status!="CLOSED"
and t.is_client_coordinator
or t.id in (select ...
- 103
1
vote
1
answer
4k
views
How to select subset JSON with PostgresQL?
I'm trying to deal with a JSON column that does not have an explicit JSON schema or convention. There are varying keys within the JSON. Some keys do not exist, some keys do exist but empty string ...
0
votes
1
answer
2k
views
PostgreSQL error: "subquery uses ungrouped column"
I have a query with subqueries that use only elements from the GROUP BY clause:
SELECT
DATE(cm.created_at) as "date",
COUNT(1) as total_message,
room_id,
(SELECT ...
- 111
0
votes
0
answers
38
views
Comparing sum against the average of all sums
I am looking for a way to compare the payroll of a team compared to the average of other teams in the same league. Here is the table structure, three standard tables and two connecting tables to ...
- 1
1
vote
2
answers
278
views
Getting a query to work on SQLite as well as on MySQL
I have a subquery with a condition in the main query where clause.
Here is the sample schema
CREATE TABLE `payments` (
`id` int(11) NOT NULL,
`discount_id` int(11),
`amount` int(11)
);
...
2
votes
1
answer
261
views
How to fetch related rows on multiple condition with ranking
I have following table for storing item information in PostgreSQL.
id | name | category | seller | m_age | status | type_id | region |
----------------------------------------------------------...
- 21
5
votes
1
answer
797
views
Include non-inserted rows as result of INSERT INTO ... RETURNING
I'm using PostgreSQL 11.
I want to conditionally insert values into a table while having the result of the insertion include a null for each row of the input which did not result in an insertion.
For ...
- 153
0
votes
1
answer
1k
views
Is there a neat way to build a user posts/likes/comments query?
Say we have table users and three tables posts, comments, likes, which are many-to-one to users. I want to build some report query for all users, even if they have 0 posts, comments or likes.
Is there ...
- 101
0
votes
1
answer
1k
views
MariaDB, SEQUENCE engine, dynamic upper bound
I'd like to generate a sequence in MariaDB starting at 1 and ending with whatever is the largest id in some other table. That other table has roughly this schema:
create table main (
id integer ...
- 145
0
votes
1
answer
1k
views
Reusing sql query values for another query in single request
I have an sql table for some chats with a table messages, containing id int pk, chat_id int, created_at timestamp.
Given a message id, is it possible to select it, along with 5 messages after it ...
0
votes
0
answers
69
views
I'm having trouble generating a list of downloadable items (data from three tables) with a column showing most recent download time for each
I am using the following query to generate a comprehensive download log with data from multiple tables (reduced to three for this example):
SELECT wp_download_log.ID
,wp_download_log....
- 23
2
votes
1
answer
5k
views
Sql Server string concat with order by and subquery
While working on some dynamic sql production code we encountered code that's concatenating a bunch of strings and a subquery and then more string concatenation. The goal of the code is to populate a ...
-1
votes
1
answer
66
views
Comparing the result of a subquery with LIKE
I need to find all the movies that have all of its actors' names to start with an A and have a 60.000.000 or more of gross. I have this but is not working
SELECT title, imdb_score
FROM Movie AS m
...
- 1
0
votes
1
answer
642
views
Select rows from table having more than two specific values
I have a table having details such that
Userpin product Time1 contains
4556 23 7:30 A
4557 23 7:30 B
4558 23 7:30 C
...
0
votes
2
answers
308
views
Inherit INNER JOIN fields in MySql SELECT QUERY
I want to know how I can join one field of an INNER JOIN table in a SELECT QUERY.
Here's what i got so far:
In this query my result would be NameA, NameB and idC, but i want to join tableA and tableC ...
- 15
0
votes
1
answer
263
views
Select rows from table having specific values depending on another values in the same table
I need to be able to select all Userpin that have tr_type 7 only but if there is tr_type 7 and 8 in other row ignore 7 and 8 example:
Userpin TrDate Time1 Tr_type
4555 12-02-2020 ...
- 3
0
votes
2
answers
522
views
Using subquery within IF condition [closed]
Hello i am having a weird problem currently i have the following query
SELECT *
FROM `txt_campaign_message` `cm`
LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
LEFT JOIN `...
0
votes
1
answer
2k
views
Will subquery (using IN) run multiple times for update statement (SQL Server 2017)
Where an update statement is updating the value returned by a subquery - will the subquery be run for each row?
UPDATE a
SET DM = 1
FROM TableA a
WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA ...
- 3
0
votes
0
answers
1k
views
Multiple sub query in insert statement for postgreSQL
I am trying to insert into table with multiple queries. And its not working, here is my work.
insert into post_topics_ta (id,post_id,topic_id,confidence_score)
SELECT * FROM post_topics WHERE ...
- 23
0
votes
0
answers
235
views
Improve MySQL query efficiency for first row in a group
I've written the query below in MySQL to get the top 10 top landing pages across all browser sessions.
Reading other similar posts about how to access the first row in a group, it seemed like the ...
- 101
0
votes
4
answers
102
views
How to visualize joins between inner queries to their respective outer queries?
I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query.
Here's an example:
SELECT NM.MovieId, NM.MovieTitle
FROM NinjaMovies AS NM
WHERE ...
- 39.5k
0
votes
2
answers
359
views
MySql LOAD DATA with subquery performance issue
I'm migrating data into a MySql (5.7.26) database (32GB ram), running as a managed service on AWS. While importing the data, I need to map one of the columns of the CSV being imported to another value ...
- 3
0
votes
1
answer
635
views
How to show multiple record if has top 1
Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-...
- 29
0
votes
2
answers
31k
views
Running a query on the results of another query?
I have 2 queries, they both run fine and pull the results I'm looking for. However, I need to be able to run one query from the results of another. This is t-sql/mssql/sybase
For example, the ...
- 153
0
votes
1
answer
60
views
Count total unique rows for each date and group by date
I have the following table:
| email | date_added |
-------------------------
| [email protected] | 2019-01-01 |
| [email protected] | 2019-01-01 |
| [email protected] | 2019-01-02 |
| [email protected] | 2019-01-02 |
| [email protected] | 2019-01-...
- 199
0
votes
1
answer
2k
views
MySQL date range if data not available display 0
I've a table here here is the structure : id,id_berita,ip_user,tanggal
I try to use this query :
select count(id) as jumlah,tanggal from tb_trafik_berita WHERE (tanggal BETWEEN '2020-01-02 00:00:00'...
- 1