i would like to join two tables that have a common column for example "custcode". The table "customer" is filled with some customer data and the table "customer_address" with the customer addresses.Basically I would like to join the data of…
Would it be possibile to calculate the daily sum of hours based on the difference between time present in same colums? date time 2017-11-08 08:25:57 2017-11-08 12:31:59 2017-11-08 13:28:42 2017-11-08 17:34:13 2017-11-09 08:28:08 2017-11-09 12:31:15 2017-11-09 13:20:13 I can get…
I am using postgres. What I have right now is like this: SELECT <statement> FROM ( <MASSIVE JOIN STATEMENT> ) UNION ALL SELECT <different statement> FROM ( <SAME MASSIVE JOIN STATEMENT> ) What I would really like is to have…
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…