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
1 vote
2 answers
48 views

Applying conditional statement in Oracle DB

In the Oracle DB there are three tables, see below. Table: 'People' Table: 'Dogs' Table: 'Cats' Here is a Fiddle with data to test : http://sqlfiddle.com/#!4/d7cb4 Depending on the result of a ...
  • 177
1 vote
1 answer
86 views

Percentage based scan on a timeseries data

I have 20 GB csv file with 650 million rows. The data looks like this: trade_time,price 2020-01-01 00:00:01.481,7189.42 2020-01-01 00:00:01.708,7189.42 2020-01-01 00:00:06.290,7189.5 2020-01-01 00:00:...
  • 13
2 votes
2 answers
298 views

Can Somebody help me understand this query

The below SQL query gets all the ids which have different last names. select * from names a WHERE a.first_name in ( select b.first_name from names b WHERE a.last_name<>b.last_name ) I am ...
  • 129
1 vote
0 answers
25 views

Getting totals from different tables to create overall total

I currently have 2 tables TableA and TableB` both within the same MYSQL Database. I am trying to find a way to take totals from both tables and add them together to give a definitive total. The tables ...
  • 117
0 votes
1 answer
196 views

How to compute the average score for each user, by time interval?

I have a table User that only has an id and a name. A user has many Games, which is a table that contains an id, a score, inserted_at and a user_id referencing the User table. I would like that my ...
0 votes
0 answers
34 views

Inconsistent query result from mysql workbench and command line client

I have noticed I am getting inconsistent results for the same query when sent via MySQL Workbench and the command line client. The query is executed on a remote server, in which one of the columns ...
0 votes
0 answers
94 views

MS Access and the Dataverse -- lost primary keys and read-only queries

I'm trying to create a database application with tables stored on a Dataverse/Teams backend and an MS Access front-end. I have a simple one-to-many database design, and a form with a query as its ...
  • 3
0 votes
0 answers
30 views

DAX Query ALLSELECTED and play axis context

On my visual, I have filters on the left, at the top a "New parameter Field" to change the legend of my graph dynamically. I display the profitability on the abcissa and the amount before ...
3 votes
1 answer
434 views

Inconsistent keyset pagination when using (timestamp, uuid) fields

I am using the keyset pagination method for uuids on my Postgres database described in this post: How to do Pagination with UUID v4 and Created Time on Concurrent Inserted Data? However, I have ...
  • 31
0 votes
1 answer
30 views

How to get the highest (max) cost, lowest (min) cost, and the date that each occurred?

I need to find the highest cost & lowest cost for each part in a table (There are many parts) in a given date range. And output the date that each cost was recorded. Example Data: CREATE TABLE ...
-3 votes
1 answer
230 views

Import 5gb sql file into MYSQL database [duplicate]

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 ...
0 votes
1 answer
166 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 ...
1 vote
1 answer
77 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 ...
  • 13
0 votes
2 answers
170 views

SQL query to return rows whose column has a common value

How can I query a Microsoft SQL table to return only the InstNum that has all Profile types C or U? Please see my table structure below. InstNum Profile 001 U 001 C 001 U 002 U 002 U 002 U 003 ...
  • 3
0 votes
0 answers
22 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"...
  • 111
-2 votes
1 answer
775 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: ...
1 vote
1 answer
954 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 ...
  • 113
0 votes
1 answer
128 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 ...
0 votes
2 answers
64 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 ...
3 votes
2 answers
6k 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 ...
1 vote
1 answer
110 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 ...
  • 493
0 votes
1 answer
467 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 ...
0 votes
1 answer
254 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=...
  • 105
-4 votes
1 answer
56 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 ...
  • 1
0 votes
1 answer
141 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 ...
  • 105
-2 votes
1 answer
102 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 ...
0 votes
0 answers
24 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 ...
  • 369
0 votes
1 answer
108 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 ...
0 votes
0 answers
18 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 ...
  • 4,301
0 votes
0 answers
51 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 ...
  • 1
2 votes
2 answers
207 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) ) ...
  • 4,301
0 votes
0 answers
10 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, ...
0 votes
1 answer
72 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', ...
  • 11
6 votes
1 answer
748 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".&...
  • 185
0 votes
1 answer
568 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 ...
  • 1
0 votes
1 answer
36 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 ...
  • 1,376
0 votes
2 answers
296 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 ...
1 vote
1 answer
2k 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 ...
  • 119
0 votes
0 answers
48 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 ...
  • 485
3 votes
3 answers
491 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 ...
1 vote
1 answer
34 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 ...
  • 29
0 votes
0 answers
49 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 ...
0 votes
1 answer
729 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 ...
  • 1
0 votes
0 answers
55 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 ...
1 vote
1 answer
435 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": "...
  • 203
20 votes
6 answers
3k 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 ...
1 vote
1 answer
69 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 ...
  • 123
0 votes
1 answer
373 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`...
  • 101
2 votes
2 answers
587 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 ...
  • 127
0 votes
2 answers
1k 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 ...
  • 101

1 2
3
4 5
29