I've a Postgres table like this where a order count for each tenant is inserted when it changes. datetime tenant_id orders 2023-09-15 22:00 tenant3 2 2023-09-16 01:00 tenant1 2 2023-09-16 02:00 tenant1 3 2023-09-16 02:00 tenant2 5 2023-09-16 03:00 tenant1…
i'm trying to create a query that allows me to get 2 counts from selects. I have a table (name "warehouses") with the following design and data: nation city italy roma germany berlin italy roma france paris italy torino italy…
I'm using PostgreSQL with the following (simplified) DB design: I have a table Auction, each auction can be handled from a Tribunal or a Court of Appeal. So that I have the table Court of Appeal (primary key = [id]…
I've been trying to make a query more efficient by bringing together counts from multiple tables. I have a schema similar to this: create table "impressions" ( id serial primary key, ip inet, ad_id integer references ad(id), user uuid references…
update loan l inner join ( select LoanId, sum(Amount) as Amount from loanpayment where LoanId = pLoanId ) as lp on l.Id = lp.LoanId set l.CurBalance = OriginalAmount + TermMonths *(OriginalAmount * MonthlyRate/100) - coalesce(lp.Amount,0) where l.Id = pLoanId ;…
I built a woocommerce sql query to list all products with their important values. Here my query: SELECT p.ID, p.post_type as Art, p.post_title AS Produkt, p.post_excerpt AS Beschreibung, t.name AS Produzent, MAX(CASE WHEN pm1.meta_key = 'faktor' then pm1.meta_value ELSE NULL…
I have a table A, where data is a json column, and timestamp is a timestamp with timezone column: timestamp data 2023-08-29 13:00:00-04 { "a_123":{ "temp":85, "uv":5, "rain":0 }, "b_123":{ "temp":85, "uv":5, "rain":0 } } 2023-08-29 14:00:00-04 { "a_123":{ "temp":70,…
I am developing a PostgreSQL Procedure using the plpython3u extension which let you use python procedural language into PostgreSQL. With the following code using plpy, I am able to retrive data form table and put it into pandas dataframe. CREATE…
SELECT wilayah,nilai,t_rkpd,tahun,periode FROM nilai_indikator INNER JOIN provinsi ON nilai_indikator.wilayah=provinsi.id WHERE tahun='2022' AND periode='08' AND id_indikator='6' When i query that code the result is like this table I try count if the nilai is less or greater than t_rkpd, using this…
I have this for query for a table of a size of 2 700 000 rows in Heroku Postgres 12.16: SELECT 1 AS one FROM "users" WHERE ( external_id = 18 AND (email = '[email protected]' OR uuid = '779c7963-67b2-43ea-b19b-028759a146dc')) LIMIT…