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 da…
Tag: common-table-expression
a query that sum the count of a row from multiple columns in SQL
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
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 i…
SQL: execution model for recursive CTEs
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
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 la…
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
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
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
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 simil…
how i can get all products from parentid in 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 catego…
Psql query : Union is not preserving the order of rows coming from cte
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 exp…