I have 2 SQL tables one holding stock information then a style table which details how the stock is used. For some jobs the information is contained with a print file so the value in the stock table would be BIN, if not it would contain a template name which would cause a query to a Styles table which then
Tag: common-table-expression
SQL few running total at different stops
Note: I am bringing up a problem to which I have a solution, the purpose of the question is to see if there is a better one. I have customers that make transactions. I want for each customer to know when did they reach 3K, 5K and 7K (or any other threshold). End result is: I was wondering if I
PostgreSQL: Using CTE with IN
Learning CTE right now, the following query is super basic and it doesn’t really have any useful value but I don’t understand why it doesn’t work The error I’m getting is “ERROR: column “cte_actors” does not exist” Currently using postgres 14 and DBeaver Answer You need to SELECT from the CTE:
“WITH AS” Working in Postgres but not in H2 dabatabse
I am writing a single query to insert data into 2 tables using “WITH AS”. The query works fine on Postgres but on H2 database it is throwing syntax error. I have 2 tables. Table 1 has 2 columns — a Primary Key table1_ID and a table1_value column. Table 2 has 3 columns — a PK table2_Id and table2_value and
How to pipe window function output directly into a new window function in SQL?
I am new to SQL and I have the following query: This doesn’t work due to no such column: MyMax. Even though from my understanding the column for MyMax is being created on the fly, I’m guessing SQL still isn’t able to use its values immediately as an input into the next window function I already tried creating the column
WITH queries with INSERT in MySQL
I’m trying to do something that I’m almost positive works in Postgresql: In other words, I want to grab the “something” row to be used for a bunch of new rows in “somwhere”. Each new entry should have a reference (like foreign key value) back to the same “something” row in the cte, mostly just to avoid a mess. However,
Postgres giving relation does not exist error for alias
Above query is working fine in MS Sql but showing this error when running it in postgres Answer In MS Sql Server it’s possible to update the CTE. In Postgresql you can link to a CTE for an update. For example: However, such update assumes that the id in that table is unique. But, to mark the newest duplicates it
Can’t JOIN on CTE column inside CTE, unknown column
I am trying to achieve the following… I get the following error: Why can’t I reference the relations table? Answer Based on your CTE, this is just a guess, but I think you probably want: We don’t know your table structures, so I can’t say I have the right columns for that join, but I’d say it’s a pretty educated
How to select data with percentages from Clickhouse?
Given the following table: I’m trying to fetch all statuses by host, with their relative percentage. In order to do that, I need to count results grouped by host and status, and divide each counted field by its total host fields count. For example, this query would have worked in MySQL: But ClickHouse throws: DB::Exception: Unknown identifier: host_total; there are
Trying to delete duplicate rows in SQL Server where the difference is the date or batch number
I have this query: Which is returning the following results ID_NUMBER INCEPTION_DATE OCCURRENCE TRANSACTION_DATE FILE_LOAD_DATE BATCH_NUM 112897732 2008-09-15 4 2008-07-03 2008-07-07 17:57:19 06341 112897732 2008-09-15 4 2008-07-13 2008-07-18 03:35:55 06753 828194721 2008-11-11 1 2008-09-06 2008-09-17 02:50:44 97334 828194721 2008-11-11 1 2008-09-23 2008-09-24 02:55:27 98331 456457422 2008-09-28 1 2008-12-03 2008-07-13 08:08:39 00734 456457422 2008-09-28 1 2008-12-03 2008-07-18 13:35:55 00991 999272910 2008-05-07