I have the following tables (simplified) in Postgres: CREATE TABLE party( id int PRIMARY KEY, family_name varchar(50) NOT NULL ); CREATE TABLE election( id int, country_name varchar(50) NOT NULL, e_type election_type NOT NULL, e_date date NOT NULL, vote_share numeric, seats…
I have a table in Postgres 14.9: Name (Txt) Detail (JSONB) state (JSONB) apple [{ "code": "156", "color": "red" }, { "code": "156", "color": "blue" }] [{ "ap": "good", "op2": "bad" }] orange [{ "code": "156", "color": "red" }, {…
I am using PostgreSQL If I want to get customer.first_name, customer.last_name or any other column in addition to customer_id and sum(total), I am getting error as shown below: select i.customer_id, cu.first_name, cu.last_name, sum(total) as amount from invoice i join customer…
I need to write a Query that gives me January 31's rolling 3 day average of total transaction amount processed per day I used the query below, but I'm having problem to retrieve information, because it keeps giving this error:…
I have this table CREATE TABLE IF NOT EXISTS public.campaign_result ( id integer NOT NULL DEFAULT nextval('campaign_result_id_seq'::regclass), state result_state NOT NULL DEFAULT 'processing'::result_state, data json NOT NULL, response json, act result_action NOT NULL DEFAULT 'none'::result_action, message character varying COLLATE pg_catalog."default",…
I have the following PostgreSQL query: SELECT extract(YEAR from date), AVG(amount) FROM transactions GROUP BY extract(YEAR from date) ORDER BY extract(YEAR from date) How can I refactor the query so that extract(YEAR from date) does not have to be repeated…
I've have started building a banking project using Java, a Spring Boot application, and a PostgreSQL database. I want to generate a random 8 or 10-digit account number each time I create a new account. What's the best way to…
I have an SQL query: select * from sval where fid = 994 and val = 'XLON'; that returns id fid val 100 994 's' 102 994 'd' 103 994 'd' 104 994 'd' 110 994 's' 140 994 's'…
I have the following table with a combo key columns CREATE TABLE IF NOT EXISTS MyTable ( ColumnID1 integer NOT NULL, ColumnID2 integer NOT NULL, ColumnName character varying(1000), CONSTRAINT "MyTable_pkey" PRIMARY KEY (ColumnID1, ColumnID2), CONSTRAINT "MyTable-ColumnID1-ColumnName-Unique-Key" UNIQUE (ColumnID1, ColumnName) )…
There is table called accounts containing transaction data with columns account_number, date, balance … I want to get the balance for each account_number where the balance<-1000000000 on the last day of each account transaction, and sort the output by balance…