Let’s say we have a Sqlite table containing: How to group by h=CONCAT(city,age) but also add in the group the users that have a column foo identical to someone in the group? As seen in Group items by 2 columns, here is how to group by h: How to add this second condition on foo? Example: here Alice and B…
Different sort results between Oracle and Postgres with Special Characters
*Apologies as I know this question has been asked before, but tried the previously suggested approaches and none fixed my issue. I am trying to compare a table found in two databases. Unfortunately, the table does not have a primary key, so I am trying to order by the same column and compare that way. When I …
Finding the intersection between two integer arrays in postgres
In postgres documentation I found that if I have two intarrays I can use the & operator to get the common elements between the two arrays(intersection), but this statement: raises this error: I have postgresql version 13 I am wondering why doesn’t this work, and how can I fix the query or try anothe…
TypeError: Argument 2 must be Tuple or List
I have built a GUI using TKinter. There I want to output the stored data and change them individually. Using the UPDATE command, I want to update the data in the table. I use MariaDB as database. I’m getting this error: Here is the complete function: Any suggestions where I’m doing wrong? Answer A…
How to Return Separate Sums of Columns if Initial Value in row is 0 in SQL
I am trying to write a SQL query that will return unique rows of sums of shipped part numbers per customer depending on if the value is negative or positive. For example: If we shipped 20 of part Z to customer A twice, and they returned 6 parts back to us, it would display: After running query, expected resul…
How to join two tables while only selecting the highest day of each month from one table
I have two tables. One with metadata, one with billing data. I need to join those effiently in order to assign metadata to costs. Table 1 (metadata) looks like this: Table 2 (billing data) looks like this: For each combination of year, month, id in Table 2, there is an corresponding ID in Table 1. For each ye…
All tables consisting of numbers less than a fixed number
I am trying to find out all the tables where table names consist of numbers less than a fixed number 16284961 at the end preceded by an underscore for example LOG_16282961. Sample User_segments table: Expected Output: Query: Using above query, although I am getting my result but additionally it also includes …
How to write a query to add a prefix to a comma separated string in MySQL?
I have a table in a MySQL database with a column id. The id column has comma separated values stored in it in VARCHAR format. I want to add a prefix C to every value in the output of the query. Ex: Answer This all sounds like a bad idea, but in some situations it might be needed. It seems
Pivot data T-SQL
I have the following table: I want to pivot it to the following table: I tried to work with the following example: https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/ But in this case a SUM operator is mandatory. I don’t want to use the SUM operator for my data. What would be the best approach …
sort with special sort number
I have two tables, sort table and assign table SortID Name 0 A 1 B 2 C 3 D I want assign mission to everyone, start with a special sortID example: start with sortID 2, and change order next week. result as below: Date Week MissionA MissionB MissionC MissionD 2021/1/3 1 C D A B 2021/1/4 1 C D A