CODEWITHSUNDEEP
sqlpostgresqlcase
tiredqa_18
tiredqa_18

Reputation: 331

How to get value from a query of another table to create a new column (postgresql)

I am new to postgres and I want to be able to set value to Y if order (order table) is a first month order (first month order table)

first month order table is as per below. It will only show the order placed by user the first time in the month:

customer_id | order_date                | order_id
--------------------------------------------------
a1          | December 6, 2015, 8:30 PM | orderA1

order table is as per below. It shows all the order records:

customer_id | order_date                 | order_id
-----------------------------------------------------
a1          | December 6, 2020, 8:30 PM  | orderA1 
a1          | December 7, 2020, 8:30 PM  | orderA2 
a2          | December 11, 2020, 8:30 PM | orderA3

To get the first month order column in the order table, I tried using case as below. But then it will give the error more than one row returned by a subquery.

SELECT DISTINCT ON (order_id) order_id, customer_id,
(CASE when (select distinct order_id from first_month_order_table) = order_id then 'Y' else 'N'
 END)
FROM order_table
ORDER BY order_id;

I also tried using count but then i understand that this is quite inefficient and overworks the database i think.

SELECT DISTINCT ON (order_id) order_id, customer_id,
(CASE when (select count order_id from first_month_order_table) then 'Y' else 'N'
 END)
FROM order_table
ORDER BY order_id;

How can I determine if the order is first month order and set the value as Y for every order in the order table efficiently?

Upvotes: 0

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

If you have all orders in the orders table, you don't need the second table. Just use window functions. The following returns a boolean, which I find much more convenient than a character flag:

select o.*,
       (row_number() over (partition by customer_id, date_trunc('month', order_date order by order_date) = 1) as flag
from orders o;

If you want a character flag, then you need case:

select o.*,
       (case when row_number() over (partition by customer_id, date_trunc('month', order_date order by order_date) = 1
             then 'Y' else 'N'
        end) as flag
from orders o;

Upvotes: 1

Popeye
Popeye

Reputation: 35930

Use the left join as follows:

SELECT o.order_id, o.customer_id,
       CASE when f.order_id is not null then 'Y' else 'N' END as flag
FROM order_table o left join first_month_order_table f
  on f.order_id = o.order_id 
ORDER BY o.order_id;

Upvotes: 1

Related Questions