Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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).

0
votes
1answer
21 views

SQL query with GROUPING SETS to get subtotals with a HAVING clause (non additive measure)

Let's say I have a (fictional) table Order with the following columns: OrderId: string Supplier: string Channel: string I want a query that counts the number of distinct suppliers per channel, ...
1
vote
1answer
15 views

SQL to merge related documents into 1 query row (MySQL)

I'm working with the following data structure: users | field| value | --- | --- | id | 123 | email| example@example.com fields | field |value | --- |--- | parentId |123 -> users.id | ...
1
vote
1answer
54 views

Count all rows for each DISTINCT

How can count all rows of WHERE statement: SELECT DISTINCT column1 FROM table COUNT WHERE date::text LIKE '%2014%'; My output: column1 ======= info1 info2 info3 Desired OUTPUT: column1|...
0
votes
1answer
29 views

Linking tables to extract value based on closest previous date

I'm going to use an example that was previously posted but modify it to match my problem: TOCs bu Date: Location StatDate TOC WC1 9/1/1900 5600 WC1 10/1/2010 5605 WC2 8/2/1980 4300 WC2 9/...
0
votes
1answer
22 views

MongoDB - Mongoose query findOneAndUpdate() doesn't update/duplicates the DB

I am trying to save and update (upsert: true - creates the object if it doesn't exist) the result of the Web Api which contains multiple arrays of data to populate the stock chart. Each time I enter a ...
1
vote
2answers
28 views

Fiscal Year dynamic function (Oracle SQL)

My Fiscal Year starts on 1st October (2017) and ends 30th September (2018) I want to write a function that will dynamically calculate first day of October. Currently I have: select Add_Months(Trunc(...
5
votes
2answers
116 views

Is there a “soft” ORDER BY / GROUP BY?

SQL-Fiddle http://sqlfiddle.com/#!9/c82c87b/1 Table Definition First of all, this is my table: CREATE TABLE `stackoverflow` ( `id` VARBINARY( 36 ) NOT NULL COMMENT 'GUID generated by PHP', `time` ...
0
votes
1answer
32 views

Two left joins where one exists?

Basically, I have table a, which has a type and type_id and want to join on either two different tables where there is a hit. So basically, if table b has a match, return the record or if table c ...
2
votes
0answers
21 views

How to write group by and having in a relational algebra interpreter?

I am practising writing relational algebra (RA) expressions in RA interpreter from Duke, but I can't figure out how to represent group by or having (from SQL). I have this expression, but I need to ...
0
votes
1answer
17 views

select Data between two date range mysql return wrong values

I want to select data between 9/Nov/2018 and 11/Nov/2018, but the result seems wrong - it returned data outside the two ranges. Using the below query... select * from radacct where DATE_FORMAT(...
0
votes
1answer
9 views

Group fields obtained after join as attributes of the main entity object

I work with Postgres 10. I have 2 entities Item and Image. An Item can have multiple Image(s) so I have a Foreign key from Image to Product. I have the following query: SELECT name, i.image, i.type ...
-1
votes
1answer
26 views

Generating a calculated column based on “passed” rows values

I'm looking for a way to add a column to my query, and it's based on the SUM of "passed" rows values of a column, something like this: DECLARE @Remain int = 0; SELECT price, @Remain += price AS ...
-1
votes
2answers
20 views

Counting Occurrences of one Column in Multiple Columns in a Different Table

I have two tables, let's call them Table1 and Table2. Table1 has a column c1. and Table2 has two columns called c2 and c3. I would like to count the occurrences of c1 from Table1 in both columns of ...
1
vote
1answer
35 views

Transpose Query result in sql server

I have a query that returns several rows (one to many relationship), I need to be able to obtain as a result a single row that has concatenated these values. This is my query: SELECT I.[ID] ...
0
votes
1answer
18 views

Use twice DISTINCT ON ina multiple join situation

I'm using PostgreSQL 10. I have an entity Product. The product has images, so I have a Foreign key from Product to Image. A Product have 1 ON 1 relationship, with a Video entity A product can have ...
1
vote
1answer
44 views

how can I convert multiple rows into one row

Imagine I have a table like this: --------------------------------------------- Name | Property_1 | Property_2 | Property_3 | --------------------------------------------- jimi | tall | null ...
0
votes
1answer
10 views

Getting number of records in mysql inconsistency

When i check the number of records in a MySQL table using "show table status like 'table_name", the number of rows given is different from the number of rows when i run "select count(*)" on the same ...
0
votes
2answers
55 views

Remove Duplicates (or Select Unique) from multiple rows, with all random data

I have a table that gets results from three different sources. Each column represents a source, and each row a result of an outcome. There are over 50k rows for a total of 150k results. I need to ...
0
votes
1answer
60 views

NodeJS problem with utf8_general_ci

I have a website with a chat system. This chat system has a table 'messages' and one of the columns is 'messagetext' set to utf8_general_ci(MySQL). In my website, I use php to query database. The ...
4
votes
1answer
75 views

Cumulative duration between value resets

--DROP TABLE Taco_Val; CREATE TABLE Taco_Val ( ID INT IDENTITY(1,1), AuditID VARCHAR(5), CreditID VARCHAR(10), TS DATETIME, Val BIT ); INSERT INTO ...
0
votes
2answers
28 views

How to do a Join with optionally matching columns

I have two tables as follows (in a Postgres 10 database): Maize: id|acreage|date |region 1 |20 |2018-10-18|A 2 |10 |2018-10-07|A 3 |26 |2018-10-18|B 4 |06 |2018-10-07|B Paddy: ...
2
votes
1answer
58 views

Build a histogram of road speed based on road length by grouping road segments

A data set look like this: The table doesn't have sum(segLength yet), but can be computed by summing up sum(segLength) from road_table group by roadID A roadID (e.i. Lake Street) belongs to a ...
1
vote
1answer
25 views

Getting the first 3 children(items) for each owner in a list of primary keys

I have two tables Item an Owner, an a Foreign Key from Item to Owner, owner_id. I'm using PostgreSQL 10. For each owner I want to get the first 3 items if exist. select "items_item"."name" FROM "...
0
votes
0answers
24 views

How to separate values from column into two columns? [duplicate]

I have to separate values into two columns Prod_id Param Val 1 201 444 1 202 222 1 203 12 1 204 53 1 205 24 How to make ...
1
vote
2answers
55 views

SQL To Fetch Partition Boundaries and Filegroups

For a given partition scheme name, I need to retrieve the set of Boundary Value FileGroup Name This must work without the existence of any tables participating in the scheme. i.e. joins via sys....
0
votes
3answers
50 views

Select only records that have different values for a particular column

In my table there are some records that have different name values for the same account number and I want to extract only the records that have different values in the name field, but that share the ...
0
votes
1answer
26 views

Query for all the Postgres configuration parameters‘ current values?

You can set various configuration parameters for Postgres by either editing the postgresql.conf file manually or by calling ALTER SYSTEM commands. These are two avenues for writing the settings, but ...
0
votes
1answer
21 views

NOWAIT equivalent in MySQL 5.7

MySQL 8.0 has a new feature to allow a transaction to timeout immediately by throwing an error if it tried to access records that are locked by another transaction instead of waiting for that ...
0
votes
1answer
36 views

mysql query to get time difference

I have query like this SELECT user_ID, tanggal, kode_layanan, nomor_antrian, aksi_ID,jam FROM `transaksi` WHERE tanggal = '2018-10-11' AND kode_layanan = 'A' AND aksi_ID BETWEEN 4 AND 9 and ...
4
votes
1answer
77 views

An UPDATE runs unexpectedly on MySQL 5.7 whereas it runs as expected on prior versions

I use a column called sequence on tables where I have to re-sort blocks of rows into an arbitrarily order. CREATE TABLE `tkmemberstage` ( `ID` bigint(22) unsigned NOT NULL AUTO_INCREMENT, `...
0
votes
1answer
31 views

Get two row counts from the same table [closed]

What I am trying to achieve is run both queries (below) at the same time. From one table rvsessions I need to get the count for the total amount of sessions(which is achieved by the number of times ...
-2
votes
1answer
39 views

Fetch 2 different columns same data in 2 different rows

I have an issue and got stuck very badly. I have a table product which has 5 columns: col1 col2 col3 col4 col5 There are a few records where the same value is repeated in different columns and in ...
-2
votes
0answers
26 views

Pull the 2 min value - mysql

I need a calculated min column using datediff & min functions but not exactly the min value but the 2nd min value or date, below is the query for your reference: Select r.rfq_Id, datediff(min(q....
0
votes
0answers
54 views

Long running SQL Server Query, eats up a lot of resource

I have already posted on stack however as recommended by the guys on there I am now putting it on here. See my previous posts: https://stackoverflow.com/questions/52850961/sql-query-long-running-...
1
vote
0answers
39 views

MongoDB query: date range (but dates in collection are in NumberLong format)

I need some help with my MongoDB query. I was trying to count in collection the number of entries for a specific date (i.e. May 05, 2016), but dates in the collection are in NumberLong data type. So I ...
1
vote
0answers
29 views

Recreating Layers based on data already entered

Background: Construction events take place on a certain block of road. The events are based on data like Lane#, Layer#, and SP and EP (Start Position and End Position). I created a picture to make ...
2
votes
1answer
71 views

Change level(s) of totals with GROUP BY

I am interested in the totals and the details of every line, in other words, the results of the second query. Is there an easier way to do a rollup, and just have grandtotals? (my 'live' query has ...
1
vote
0answers
38 views

ERROR: invalid input syntax for type oid: “english”

While creating an index in Postgres I am getting an error: invalid input syntax for type oid: "english" LINE 1: ...s ON entity_attributes2 USING gist ( (to_tsvector('english',... The query ...
0
votes
0answers
19 views

Database schema design to send unique messages to user

We are working on a system that users (hustler) can post their skills, and based on their listed skills, a client can try to hire them to assist them on a project. If a client enters specific text ...
0
votes
1answer
36 views

SQL Percentage of users falling in a specific range

I have a list of users with corresponding date of birth. Is there any possibility I can get a percentage of users born between 1990 and 2000 in a single SQL query? The query I tried is as follows: ...
0
votes
2answers
82 views

Why is this query throwing an “Invalid Column Name” error? [closed]

Trying to run a simple UPDATE query as follows: UPDATE [TILL].[dbo].[appParameters] SET [TILL].[dbo].[appParameters].[ParameterValue] = "1" WHERE [TILL].[dbo].[appParameters].[ParameterName] = "...
1
vote
1answer
25 views

how to extract difference between timestamps

SQL greenhorn alarm: I have a table with timestamp information (44 rows) and I now would like to calculate and extract the delta between any two consecutive rows. How can I do this? I select my data ...
2
votes
1answer
67 views

How can I make this nested query more efficient?

I have 3 tables: Room, Conference, and Participant. Room has many Conferences, and Conference has many Participants. I need my query to display the fields from Room, as well as the number of ...
0
votes
1answer
35 views

Return 2 rows at a time from SQL Server

How can I slice up my table and return and evenly distribute the data? Because I wanted to slice this up and put it into my 3 combo boxes in a windows form. Example : Table1 ID Name ...
0
votes
1answer
29 views

Subtract multiple quantities in table2 from table1

How can I subtract the quantities in table2 from table1? I'm using SQL Server 2014. Table1 P_ID name quantity 1 carrot 100 2 eggplant 100 3 Avocado 100 ...
1
vote
0answers
11 views

Calculate percentages by using JOINS result in postgresql

I am trying to calculate percentages by joining 3 tables data to get the percentages of positive_count, negative_count, neutral_count of each user's tweets. I have succeeded in getting positive, ...
1
vote
0answers
28 views

InfluxDB - How to get start timestamp of alternating series

I have these data series in an influx database, alternating between 0 and 1. 2018-10-02T07:12:00Z 0 2018-10-02T07:15:00Z 0 2018-10-02T07:18:00Z 0 2018-10-02T07:21:00Z 0 2018-10-02T07:24:00Z 0 2018-10-...
0
votes
0answers
21 views

find out postgresql query progress [duplicate]

I have ran an UPDATE query in postgresql. After 5 days it has not finished yet. Is it possible to check percentage of completeness of my query which I have ran previously. I want to know if it takes ...
0
votes
2answers
32 views

Getting @@ROWCOUNT when there is an insert query in SQL-SERVER?

Hope you are doing well. I have a procedure as you can see below : ALTER Procedure [dbo].[OpenExcel] @TableName varchar(2000), @FileName varchar(2000), @ResultOpenExcel tinyint output AS ...
0
votes
1answer
30 views

Query to filter out data based on two logic

This is a sample table: sample_id | timestamp | p_id ============================================ 62054 | 2018-09-25 10:18:15 | 2652 62054 | 2018-09-27 16:44:57 | 966 ...