Tag: common-table-expression

Converting PostgreSQL to MySQL

I am following a tutorial on how to find recurring transactions in a bank statement dataset. I have all the data needed, but I have issues getting the queries to work with MySQL. Any idea on how to convert this to MySQL? The error is: Update I adjusted the SQL query based on feedback, and providing example data. Now I’m

a query that sum the count of a row from multiple columns in SQL

common-table-expression select sql sql-server

I have to write a query that sum the count of a row from multiple columns My code is like this, but total doesn’t work: input: output: Answer Use coalesce as follows:

SQL – An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause

aggregate-functions average common-table-expression sql sql-server

If you see the picture below, I only want to see those categories where the average revenue is greater than the overall average. The query that I’m using is below. The very last line is causing the issue. The error that I’m getting is: An aggregate may not appear in the WHERE clause unless it is in a subquery contained

SQL: execution model for recursive CTEs

common-table-expression recursion sql

I’m trying to understand how recursive CTEs are executed and particularly what causes them to terminate. Here’s a simple example: WITH cte_increment(n) AS ( SELECT — Select 1 0 UNION …

SQLite book shop: Last students and all books they ordered

common-table-expression join sql sqlite

forum. I’m trying to run SQL for my local book-shop. The problem is to show last N students and all books they ordered. My try: Is it okay code? Syntax check only shows mistake in a row 8, which I don’t get. Thank you in advance Answer You can join books to orders and filter the results for the last

Querying a cte – The multi-part identifier could not be bound. How do I fix this so I can query the table? New to SQL accounting background

common-table-expression greatest-n-per-group inner-join sql

I am trying to extract a dataset which joins 3 tables Employee E AbsenceTypes AT AbsenceBalance AB In the data set I need the most recent record from AB.BalanceTime for each AB.EmployeeUID by AB….

Best way to restoring Audit table without existing primary keys – postgres

common-table-expression postgresql sql sql-update

Table A has an audit table that I need to restore a specific column from. Rows were deleted from table A, I then regenerated them and via matching on specific times (these are appointment records) I’…

recursively creating multiple rows for every date between two dates in one row

common-table-expression datetime postgresql recursive-query sql

I have staff holidays (rdos) stored in a single row, I am very close to recursively expanding them to show a full row for each day of leave. Running this on Postgres Database version 10.10 Data Set: My query currently: I am getting duplicate regressions and unwanted cross matches. Any ideas? I have seen similar solutions that rely on a

how i can get all products from parentid in SQL Server?

common-table-expression hierarchical-data recursive-query sql sql-server

I have a category table to name TBL_category And includes the following fields: nid i identity field and i have a production Table TBL_Productions and foreign key is to nid And includes the following fields: productid is identity My question is: How can I display all products, when user selected mobile category ? I have nothing to do with the

Psql query : Union is not preserving the order of rows coming from cte

common-table-expression postgresql sql sql-order-by union

I have this query: cte1 is ordered by salary column but cte2 is not. I want the results of cte2 to get appended to the results of cte1 while preserving the order of results from cte1. But it’s not happening. If I run the above query without union with the second cte, the results are displayed in the expected order