Given below table in Postgres: id some_col 1 a 1 b 2 a 3 a I want to get output as id and true (if at least one row with that id is present in the table) or false (if no rows with that id are found in the table). For example where id in (1, 2, 3, 4, 5):
Tag: sql
Expanding jsob array in PostgreSQL to produce analytics
Imagine that we have the following table using jsonb on PostgreSQL: And the following data is available: Is it possible to expand the JSON array to 2 columns similarly to what PowerBI expand does and get the following result? firstName surName bookName William Shakespeare Hamlet William Shakespeare Juliet Aga…
Nested SUM with OVER clause
I am trying to understand how nested SUMs with OVER clause work in SQL. Let’s say i have the following schema in my DB and suppose that i want to calculate the (number of deliveries of each region)/(number of deliveries of the state) on the same year. The query to do the calculation is: I can’t un…
How to query the percent of totals in a group by query
I have a query that pulls the total number of records grouped by the year and length of characters in the identification number. I want to add a column that calculates the percentage of this total against the total for each record year. This is the query that I am using, and so far I’m able to pull the …
Interpolate Multiseries Data In SQL
I have a system that stores the data only when they are changed. So, the dataset looks like below. data_type_id data_value inserted_at 2 240 2022-01-19 17:20:52 1 30 2022-01-19 17:20:47 2 239 2022-01-19 17:20:42 1 29 2022-01-19 17:20:42 My data frequency is every 5 seconds. So, whether there’s any times…
SQLite – calculate percentage increase in values of a numeric column
I am interested in getting the % increase or decrease of the values in one column with respect to the previous value. Can someone please advice how I should do this? Thanks This is what my table looks like And this is what I would like Answer Use LAG() window function to get the previous value of sales for ea…
How to group results according to range of datetimes on another table records
I have two tables: BusinessViews: id createdAt businessId userId 1 2022-01-19 18:00:00 1 1 2 2022-01-19 18:02:00 1 1 3 2022-01-19 18:05:00 1 1 4 2022-01-19 18:05:50 1 1 5 2022-01-19 18:07:00 1 1 6 2022-01-19 18:08:00 1 1 7 2022-01-19 18:10:00 1 1 BusinessClients: id status createdAt userId createdBy businessI…
extract year_month from a date column along with other columns in a sql table
I have a sql table with multiple fields including (but not limited to the following): member_id, visit_date (in datetime format eg: 2016-01-01), visit_yr_qtr (as string, eg: 2016_Q1), purchase_item (ID number), and item_price (in US dollars). I would like to extract these fields, but also include year_month (…
Multiple column updates in one statement – Microsoft SQL Server
I am trying to update three columns depending on three different conditions in Microsoft SQL Server. See the following example for details How would I refactor the query if I want to do it in one update statement? Appreciate your help! Answer You don’t really need the where clauses. And we can take adva…
vb.net select statement failing
I have this code to populate a DataGridView (DGV) the goal is to only populate the DGV with data from the LinkTable where one of its fields ytSiteType is equal to a global variable set on another form. The variable is gvSLT and I have tested that gvSLT makes the trip to the new form with the correct value. I