I am trying to calculate the total credits for each studentID in the enrollment table. I have some repeated studentIDs which means that the same student is already enrolled in many sections and each section has different credit. When I tried these queries It made all studentIDs have the same sum of credits as…
Tag: sql
How to create a public id?
I have a database. As you can see the primary key is an auto_increment and is also unique. I read that publically sharing a row’s primary key of a table to the public is unsafe. I want to assign each row in customers a unique ID that I can publically share. How can I do this without having to specify
selecting from multiple tables in SQL without a join
Complete SQL novice, trying to make sense of the following TPC-H query: I was under the impression that you can only select from different tables after a join. However I don’t see any joins here. Is it implicit? Answer The comma is archaic syntax for the CROSS JOIN. It is essentially the same thing as a…
Create view with JSON array based on many to many table
I have a typical table with users. I have also a many to many table where first column is UserId and second BusinessId. I want to create a view with users where their businessId will be as json. I tried something like this: But in view I get this: Id BusinessEntityIds 1 [{“BusinessId”:1925},{̶…
How to include CHECK in JPA @Column columnDefinition?
I am trying to figure out syntax for columnDefinition in Column annotation when i want to add CHECK. So far what i have tried: Using JPA 2.2 (JavaEE 8) and openjpa as jpa provider. Could not find anything in docs: https://javaee.github.io/javaee-spec/javadocs/javax/persistence/Column.html Error i am getting: …
Is there a way of computing numerical integration on PostgreSQL?
I’ve got a table with the following columns: timestamp, name and activePower. I wanted to compute power consumption based on that and add it to a Grafana line chart. Right now, what I’m doing is a cumulative sum, like the following: Unfortunately, the intervals of timestamp are not regular, and I …
Query to return all values from next month
I have a dataset of subscriptions in a table that looks something like this: Table: UserID Subscription_type Subscription_end_date I’m trying to build a report in SSRS that shows a list of all subscriptions that are going to expire somewhere in the next month. The report gets sent on the first of every …
Proper Case in Athena SQL
I’m new to Athena and trying to convert a field ‘Country’ to Proper Case. For example: united states -> United States Is there a way to do this in Athena? Answer Turning a comment into an answer: Athena engine version 2 gives in its RegEx section as example for regexp_replace(string, patt…
fetchall() not working even with values in database
I am working in python with sqlite3 and I am trying to create a database. I created a table: c.execute(“CREATE TABLE persoane (nume text, varsta integer, salariu integer)”) Then I added the values into the table: c.execute(“INSERT INTO persoane VALUES(‘David’, 16, 1000)”) W…
Update data based on the previous row in postgresql
For rn (row number) = 1, x and y always stays as 0. Now I need to update the rest of x and y columns based on the above scenario(IF ..ELSE). I am unable to update the next row without updating the previous row. Is there a way to do this in a query Answer You can use a recursive