Questions tagged [query]

Writing queries to request or change information stored in a database or other information management system. Questions should include table and index definitions, sample data, expected output, and a tag for the specific system (e.g. MySQL, PostgreSQL, Oracle, SQL Server, MongoDB).

Filter by
Sorted by
Tagged with
-2 votes
0 answers
18 views

Import 5gb sql file into MYSQL database

i have exported sql file using mysql dump from a database and i am trying to import this big 5gb file into anohter mysql database. I am using mysql -u username -p -h hostname <file.sql command ...
user avatar
0 votes
1 answer
24 views

Amount of clients that have subscription by years and month

I got 2 tables as follow CREATE TABLE subscriptions( client_id INT, open_date DATE, close_date DATE ); CREATE TABLE calendar(date DATE); subscription table contains data about clients ...
user avatar
1 vote
1 answer
32 views

A query containing multiple joins becomes too slow when adding one more condition after the joins

I have this query that finishes in less than a second on average: SELECT a.trea_code, a.fin_year, a.bill_no AS tran_id, ((('Transfer FROM ' :: text || (a.ddo_code) :: text) || 'ON Bill ...
user avatar
  • 13
0 votes
2 answers
37 views

SQL query to return rows whose column has a common value

How can I query a Microsoft SQL table to return only an InstNum that has all Profile type 'C' or 'U'? Please see my table structure below. I tried : Select InstNum from Table Where Profile = 'C' Group ...
user avatar
  • 3
0 votes
0 answers
19 views

How to make PG account for "empty months"?

I'm trying to return the average amount of money I've invested in Bitcoin in the last six months. I have this: SELECT avg("amount") FROM ( SELECT date_trunc('month', "timestamp"...
user avatar
  • 111
-2 votes
1 answer
45 views

Row_number to not increment if values on three columns are the same from the row before

Suppose I have a dataset that looks like the screenshot below and I need to add a count column for the rows based off the orderid, DocumentNo_, and CurrPackageTrackingNo with the following logic: ...
user avatar
1 vote
1 answer
37 views

Postgres "unnesting" multiple json arrays simultaneouesly with different depths

I am trying to explode/unnest a row of jsonb arrays into individual rows and keep any rows that would result in nulls. Fiddle: https://www.db-fiddle.com/f/pgd6E5yKf8NzPtgboeoC6c/7 Input table: (I am ...
user avatar
  • 113
-3 votes
0 answers
22 views

SUM within a subquery

I have a request where someone wants to see the average hours it took to make a product over a given date range. There are multiple labor records for each job, so I need the sum of those hours within ...
user avatar
0 votes
1 answer
18 views

MySQL - Getting the percentage of an occurrence

I'll do my best to explain what I'm trying to accomplish. I have a table called "Actions" and in Actions I have a column called "Resolution". In resolution some the entries, as an ...
user avatar
0 votes
2 answers
55 views

SQL Server - Finding Parent Source from T-SQL Snippet

The environments for which I'm responsible have some pretty substantial plan non-reuse challenges. I've run across the following query (h/t Brent Ozar blog commenter Michael J Swart) that does a fine ...
user avatar
3 votes
2 answers
69 views

How to Join two tables with SUM and GROUP BY in SQL server

I have 2 tables product id designation 1 coca 2 Pepsi 3 Fanta 4 Seven 5 Eight 2)sub_product product_id name quantity 1 sm 10 1 lg 10 1 xl 20 2 1L 10 2 2L 20 2 5L 20 3 Ty 10 3 Sa 20 4 ...
user avatar
0 votes
1 answer
24 views

Microsoft SQL server management studio : insert label in SQL query results

I'm working with SQL server, using Microsoft SQL Management Studio. I'm writing quite some SQL queries, one after the other. This causes a list of results, which are quite difficult to distinguish, as ...
user avatar
  • 333
0 votes
1 answer
46 views

SQL query to group results on matching record ID

I am having trouble designing a query. I have a list of PersonIds for records in a Persons table. I would like to have a single query that takes this list of PersonIds and returns other records in ...
user avatar
0 votes
1 answer
46 views

Forcing a sequential scan over the index scan

I have this query select s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment from part,supplier,partsupp,nation,region where p_partkey=ps_partkey and s_suppkey=...
user avatar
  • 95
-4 votes
1 answer
45 views

I need help with fixing this query please!

I need to figure out what is wrong with this query. I get this error when running the query: "Msg 8169, Level 16, State 2, Line 1 Conversion failed when converting from a character string to ...
user avatar
  • 1
0 votes
1 answer
21 views

mssql - query with results from same table and association table

I need help with a query. I have 3 tables: "Parts" table. one important thing to note here is that, the parts table contains both main part and child part. Child parts are components of ...
user avatar
  • 105
-2 votes
1 answer
58 views

Create procedure using SELECT sum and JOIN

I am using ADO.net with SQL server. I created a stored procedure to get multiple records count. The other queries return the correct results, but the last two queries, which count the total amount of ...
user avatar
0 votes
0 answers
13 views

Finding duplicate records in a subset

I have 3 tables: users orders order_items I am trying to get a list of all orders by the same user that have the exact same order_items.item_id within a 24 hour period representing the user having ...
user avatar
  • 359
0 votes
1 answer
32 views

Why my simple searching query is too slow?

I don't know why searching in my "cities" table is so slow. My query looking for a table "cities" located about 25km from the city. I use this simple query and the database takes ...
user avatar
0 votes
0 answers
15 views

Deleting rows from a tag_map if parent tag exists

I have a simple tag_map CREATE TABLE tag_map ( article_id int(11) unsigned NOT NULL, tag_id int(11) unsigned NOT NULL, INDEX(tag_id), PRIMARY KEY(article_id,tag_id) ) ENGINE=InnoDB There is a table ...
user avatar
  • 4,179
0 votes
0 answers
49 views

SQL 2014 v12 server - How to calculate how old is a CPU on a device

Trying to do a query to find out how old is the CPU on the device. We do have a way of doing it but it involves a manual step which we are trying to move away from. Basically now we are taking the CPU ...
user avatar
  • 1
2 votes
2 answers
38 views

Finding gaps between the values of a column in MySQL

At a table of movie actors, CREATE TABLE ArtistMap ( ArtistID int(11) unsigned NOT NULL, MovieID int(11) unsigned NOT NULL, Year year, INDEX(MovieID), INDEX(Year), PRIMARY KEY(ArtistID,MovieID) ) ...
user avatar
  • 4,179
0 votes
0 answers
9 views

Is there any way to make this query spatialite more efficient?

I have two tables, The "Sitios" table which has point type geometries. And the table AdministrativeUnit that has the multi-polygons that conform a map, with its municipalities, provinces, ...
user avatar
0 votes
1 answer
36 views

SQL query ORDER BY gives different result every once in a while (5 second interval)

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_product dg ON dg.post_id = wp_posts.ID AND ( dg.location IN ('XV', ...
user avatar
  • 11
6 votes
1 answer
732 views

Unused columns in WHERE clause

I found this query in our codebase: DELETE FROM "Foo" WHERE ("Foo"."Id", "Foo"."CreatedAt") IN (SELECT "f"."Id", "f".&...
user avatar
  • 175
0 votes
1 answer
22 views

Combine multiple rows into one single column (MS Access)

I am trying to build a query from a table (Tbl_XYZ) that will give me the information for the primary key all in one row rather then creating dups for that primary key and showing up with multiple ...
user avatar
  • 1
0 votes
1 answer
28 views

Query to update one column based on the amount of another column

I have a table with below structure : create table Z_TEST_ONE ( col_id NUMBER, bed_amnt NUMBER, bes_amnt NUMBER, bop NUMBER ) Sample data is like this : col_id bed_amnt ...
user avatar
  • 1,294
0 votes
2 answers
50 views

How to manipulate the output of a GROUP BY CUBE query changing the NULL values generated for the groups?

I wrote a query to sum salaries grouped by Country and Gender. I don't want the rows to show NULL or empty values for the totals, instead I want it to show Total and Grand Total to better describe the ...
user avatar
0 votes
1 answer
55 views

select json b field of array postgresql of an array field in json

*Postgresql version: PostgreSQL 13.5 alter table accounts add tags jsonb default '[]'::jsonb not null; select DISTINCT AC.tags from accounts as AC where account_type = 'TEAM'; this query ...
user avatar
  • 109
0 votes
0 answers
30 views

replace duplicate column values only on one column, from second occurrence onwards

I have a simple query in Oracle 11g select ID,Section,Capacity,Output from yeilds order by section; that returns data in this form ID Section Capacity Output 2 SectionA 45 30 3 SectionA 45 25 4 ...
user avatar
  • 465
3 votes
3 answers
245 views

Query result precedence if filter columns have values

I have a table "customer_config" with these columns: company (varchar) warehouse (numeric) section (numeric) config_keyword (varchar) config_value (varchar) The two config_* columns can ...
user avatar
1 vote
1 answer
27 views

Help with nested query for counts

I have a table within my DB that contains, amongst a few other columns which are used for restricting the result set, the following details: Report_Reference Valuation 12345 A 12345 A 12345 AA ...
user avatar
  • 29
0 votes
0 answers
17 views

Translation Fallback query

Hello I am trying to use the WordPress database without the WordPress therefore I'm writing some manual queries with Laravel's ORM eloquent, anyways I was trying to implement the fallback translation ...
user avatar
0 votes
1 answer
43 views

PostgreSQL get json key name after comparing values of keys

I have table which has a column of type json with name "demographic". I want to compare keys(predefined by me) by their values and the result of that should be the name of the key and not ...
user avatar
  • 1
0 votes
0 answers
19 views

count occurrences of a condition between two dates inside a projection in createCriteria

I am writing this query in grails using createCriteria but I can't figure out how to get the count on a between condition for one of the projections. This is the code I got so far. def ...
user avatar
1 vote
1 answer
54 views

Unique array values for this string_to_array

This is a follow-up to: Best way to map different JSON keys to same target columns Based on these sample tables: data_providers: id | field_map -------------- 1 | {"segments": "...
user avatar
  • 203
19 votes
6 answers
2k views

Is there a generic term for tables and views?

I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso. As far as I understand, 'entity' is ...
user avatar
1 vote
1 answer
28 views

How to address aggregate fanout in the below query

I have a table user. Every user has a one-to-many relationships with topic_likes and topic_dislikes. I want to query for a user and have their one-to-many relationships exist in their own column in ...
user avatar
  • 123
0 votes
1 answer
98 views

FQN columns clash in HAVING clause - Unknown column error

Having this MySQL query SELECT `books`.* FROM `books` LEFT JOIN `authors` AS `author` ON (`books`.`author_id` = `author`.`id`) LEFT JOIN `publishers` AS `publisher` ON (`books`.`publisher_id`...
user avatar
  • 101
2 votes
2 answers
213 views

Can you include the columns of two separate tables in the result of a sql query?

I am trying to order the results of a sql query of one table using the column of another table. Is it possible to include this column from a different table in my results? For example, this query uses ...
user avatar
  • 127
0 votes
1 answer
187 views

Postgres: How to automatically fix date/time field value out of range inside the query

How one can make Postgres treat a little stupid about the date which actually does not occur on the Gregorian calendar? SELCET date, value FROM table WHERE date BETWEEN 2022-01-01 AND 2022-02-31 ...
user avatar
  • 101
0 votes
1 answer
36 views

What is the correct usage of IN and ORDER BY keywords when used in conjunction with subqueries?

ERD for my simple db design: I want to order children based on their parent's last name. I am trying to use the following query: SELECT * FROM children WHERE parent_id IN (SELECT parent_id ...
user avatar
  • 127
0 votes
2 answers
31 views

Fastest query to process data in small batches without repetition

I have java app that is using MySQL in the backend. I have the following table: A = int, B = varchar, C = timestamp A | B | C 1 | 100 | 2022-03-01 12:00:00 2 | 200 | 2022-03-01 12:00:01 3 | 100 | ...
user avatar
2 votes
1 answer
56 views

Do UPDATEs block SELECTs?

I need to query a frequently updated record, does it wait until all updates finish? /updateUser/1 Many requests are sent UPDATE users SET count = count + 1 WHERE id = 1 LIMIT 1 /getUser/1 While the ...
user avatar
-2 votes
2 answers
24 views

Get count of unmapped records when using a mapping table

I have 3 tables as such Product Product ID Product Details 1 ... 2 ... 3 ... Vendor Vendor ID Vendor Details 1 ... 2 ... 3 ... Product Vendors Product ID Vendor ID 1 1 2 1 1 2 2 2 3 2 ...
user avatar
  • 107
4 votes
1 answer
49 views

Run multiple, expensive, single result queries and return a single row

I have a lot of fairly large datasets in a MS SQL table, and I want to get a bunch of data about. For example, let's say I have Name Age Height Weight Gender Country Person 23 186 79 Male GB ...
user avatar
  • 141
1 vote
0 answers
71 views

SQL Server- Duplicate rows n times for all tables in a database

I have a db with over 150 tables which I need to artificially inflate for stress-testing. So basically I've built an app which extracts the said db, but am currently working with a snippet of what the ...
user avatar
-1 votes
2 answers
126 views

SQL (Postgres): query array Column which must be a subset of an array

I have a table "article" and for each entry there are some "tags" stored (array column) name | tags test1 | {t1,t3} test2 | {t2,t3} test3 | {t3} test4 | {t1,t5} test5 | {...
user avatar
2 votes
1 answer
40 views

Getting how many days users have logged in during a month (same day counts as 1 log in)

Let's see if you can help me with a query. I want to know how many times users have logged in during a month. If I have this data: id_log id_user timestamp 1 1 2022-02-22 00:00:00 2 1 2022-02-22 15:...
user avatar
  • 23
0 votes
1 answer
116 views

How to handle possible NULL in a Trigger Updating a table without a Primary Key

I'm using Postgres Triggers to keep two tables in sync for a short term migration. However, I have one join table that doesn't have a primary key, but two columns that function as a composite primary ...
user avatar

1
2 3 4 5
26