I’m building some basic stats/reporting into a CRM system built in php/mysql. Consider the query: This is working so far to return the desired statistics but I am struggling for the best way to identify new students. For the purposes of this report a new student would be any user_id for whom the current…
SUM aggregation do not work on empty subqueries with DRUID
Assuming I am executing the following two queries on Druid: I tried to combine both counts in one single query with a sum aggregation like the following: And it works well UNLESS one of the two counts returns 0 rows. In that case I receive a Query returned no data message and I don’t have any final resu…
Date range to row in postgres
I have a table in postgres like this: id open_date close_date 5 2006-08-04 2019-12-31 There exist 4897 days between them. I need to turn the date range to date to have one record per day. For example: id open_date close_date valid_date 5 2006-08-04 2019-12-31 2006-08-04 5 2006-08-04 2019-12-31 2006-08-05 5 20…
PostgreSQL – how to have the 0 values using a WHERE?
i am currently working on PostgreSQL having 2 data tables : item and order. I want to display the number of order for each item in a period of time, but i would like to keep the name displayed if it’s 0. i’m actually doing: The problem is that my results are: And i would like to have: I have
Implement batchMerge() in JOOQ
I need to “upsert” a List of items. If an item’s id and anotherId is already existing, other values should be replaced by the new item. If it’s not, item should be inserted as new record. I came across JOOQ’s batchMerge and I thought it is similar to “upsert”. Unfortu…
Check if procedure exists in any database SQL Server
I’d like to know if there is any way to search for a procedure in all the databases of a server (there are only SQL Server databases on that server). So far I’ve only found how to find a stored procedure in a certain database with Object_id(), but it would take too long for me to manually search e…
Sqlite3 in Python not fetching data
I have a sqlite3 DB with a few rows in it. When I try to fetch data from it in Python, fetchall returns an empty list. The data is being inserted in a different part of the project fine. I verified this by opening the DB in “DB Browser for SQLite” and running the following command. This returned t…
SQL: How to select a single row based on a column matched against multiple possible values with preferred match order?
Using MariaDB. Say you have a table, one of the columns is color and is the primary key so it’s unique. I want a select statement to get one row based on color but i want to search for three possible color matches in a preferred order. A normal WHERE color=’red’ OR color=’blue’ O…
SQL combining tables to single rows
A problem with combining tables into single rows. TABLE GAMES: ID player1_ID player2_ID des 11 23 68 something 82 34 23 whatever 302 13 34 mmmmmmmm TABLE PLAYERS: ID alias 23 Jeex 34 Jack 68 Jill 13 John The key I start my query with is one of the Player ID’s. I want to show all the games that Player
Split text and digits by delimiter using regexp splitpart
From the table below I would like to split the config_json column into 2 columns as below The regex part of the code currently looks like this: However, there are some remains such as below: Thus, the formula above still needs to split by not just true,false or null, but also by digits or string. How can I fi…