CODEWITHSUNDEEP
postgresqlmerge
Sathesh S
Sathesh S

Reputation: 1323

Merging columns from 2 different tables to apply aggregate function

I have below 3 Tables

Create table products(
  prod_id character(20) NOT NULL,
  name character varying(100) NOT NULL,
  CONSTRAINT prod_pkey PRIMARY KEY (prod_id)
)
Create table dress_Sales(
  prod_id character(20) NOT NULL,
  dress_amount numeric(7,2) NOT NULL,
  CONSTRAINT prod_pkey PRIMARY KEY (prod_id),
  CONSTRAINT prod_id_fkey FOREIGN KEY (prod_id)
  REFERENCES products (prod_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)
Create table sports_Sales(
  prod_id character(20) NOT NULL,
  sports_amount numeric(7,2) NOT NULL,
  CONSTRAINT prod_pkey PRIMARY KEY (prod_id),
  CONSTRAINT prod_id_fkey FOREIGN KEY (prod_id)
  REFERENCES products (prod_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)

I want to get the Sum and Average sales amount form both the tables(Only for the Selected Prod_id). I have tried the below code but it's not producing any value.

select sum(coalesce(b.dress_amount, c.sports_amount)) as total_Amount
from products a JOIN dress_sales b on a.prod_id = b.prod_id 
JOIN sports_sales c on a.prod_id = c.prod_id and a.prod_id = ANY( {"123456","456789"}')`

Here 1000038923 is in dress_sales table and 8002265822 is in sports_sales.

Upvotes: 0

Views: 33

Answers (2)

Gary - Stand with Ukraine
Gary - Stand with Ukraine

Reputation: 7561

If you have a product that appears in both tables you can use a subquery that can handle both dress_amount and sports_amount values.

select sum(combined.amount), avg(combined.amount)
from
  (select prod_id, dress_amount as amount from dress_sales
   union all
   select prod_id, sports_amount as amount from sports_sales) combined
where
   combined.prod_id in ('1','2');

Upvotes: 1

stas.yaranov
stas.yaranov

Reputation: 1787

Looks like your product can exist in only one table (dress_sales or sports_sales).

In this case you should use left join:

select
        sum(coalesce(b.dress_amount, c.sports_amount)) as total_amount,
        avg(coalesce(b.dress_amount, c.sports_amount)) as avg_amount
    from products a
    left join dress_sales b using(prod_id)
    left join sports_sales c using(prod_id)
        where
            a.prod_id in ('1', '2');

If you use inner join (which is default) the product row will not appear in the result set as it will not be joined with either dress_sales or sports_sales.

Upvotes: 1

Related Questions