0
SELECT partner_id 
FROM trip_delivery_sales ts 
WHERE ts.route_id='152' 
GROUP BY ts.partner_id

From the query we can get the partners id.Using that partner id we want check in trip delicery sales lines table and want to find each customer last two sale product quantity sum. If last two sale have product qty as 2 & 5 want result as partner_id | count as Mn2333 - 7

here fore example i take partner id as 34806. But i want to check all partner_id obtained from last query

SELECT product_qty 
FROM trip_delivery_sales_lines td 
WHERE td.partner_id='34806' 
  AND td.route_id='152' 
  AND td.product_id='432' 
ORDER BY td.order_date DESC 
LIMIT 2
Shahal
  • 115
  • 1
  • 12
  • What is the relation (foreign keys?) between `trip_delivery_sales` and `trip_delivery_sales_lines`? They both seem to have `partner_id` and `route_id` columns. – Bergi May 27 '21 at 10:18
  • parter_id is the foreign key..dont want to check route_id in 2nd query – Shahal May 27 '21 at 10:23
  • You mean the same partner might have different route ids in the two tables? It would help if you could post example data of both tables, and the desired result. – Bergi May 27 '21 at 10:28
  • A partner would have only one route id. so there is no need to check the route id in 2nd query – Shahal May 27 '21 at 10:31

2 Answers2

0

You can run this query

SELECT td.partner_id,sum(product_qty) 
FROM trip_delivery_sales_lines td,
(SELECT partner_id FROM trip_delivery_sales ts WHERE ts.route_id='152') as ts
WHERE td.partner_id=ts.partner_id
AND td.product_id='432' 
GROUP BY td.partner_id
ORDER BY td.order_date DESC 
LIMIT 2

Or this one

with ts as (SELECT distinct partner_id FROM trip_delivery_sales WHERE route_id='152')
SELECT td.partner_id,sum(product_qty)
FROM trip_delivery_sales_lines td,ts
WHERE td.partner_id=ts.partner_id
AND td.product_id='432' 
GROUP BY td.partner_id
ORDER BY td.order_date DESC 
LIMIT 2
Philippe
  • 1,714
  • 4
  • 17
0

You might be looking for

SELECT DISTINCT ts.partner_id, ARRAY(
  SELECT product_qty
  FROM trip_delivery_sales_lines td 
  WHERE td.partner_id=ts.partner_id
    AND td.product_id='432' 
  ORDER BY td.order_date DESC 
  LIMIT 2
) AS product_qty_arr
FROM trip_delivery_sales ts 
WHERE ts.route_id='152'

or just

SELECT
  partner_id,
  array_agg(product_qty ORDER BY order_date DESC) as product_qty_arr
FROM (
  SELECT
    td.partner_id,
    td.product_qty,
    td.order_date,
    row_number() OVER (PARTITION BY td.partner_id ORDER BY td.order_date DESC)
  FROM trip_delivery_sales_lines td
  JOIN trip_delivery_sales ts USING (partner_id)
  WHERE ts.route_id='152'  
    AND td.product_id='432'
) AS enumerated
WHERE row_number <= 2
GROUP BY partner_id

See also PostgreSQL: top n entries per item in same table or Optimize GROUP BY query to retrieve latest row per user

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • in 1st option it shows error: ERROR: column "td.order_date" must appear in the GROUP BY clause or be used in an aggregate function LINE 7: ORDER BY td.order_date DESC – Shahal May 27 '21 at 13:54
  • in 2nd: ERROR: missing FROM-clause entry for table "td" LINE 10: WHERE td.route_id='152' – Shahal May 27 '21 at 13:56
  • Could you create [a fiddle](https://dbfiddle.uk/?rdbms=postgres_13) with your table definitions (and, optimally, sample data)? If fixed the two mistakes you mentioned thogh – Bergi May 27 '21 at 15:22
  • https://stackoverflow.com/questions/67749631/postgressql-query-comparing-two-tables-and-obtaining-its-result-and-compare-it ......... UPDATED THE QUESTION HERE – Shahal May 29 '21 at 09:09
  • Ah, so your `trip_delivery_sales_lines` tables doesn't have a `route_id` column, as your second query suggested? – Bergi May 29 '21 at 11:02
  • yes,only partner_id..it is the foreign key – Shahal May 29 '21 at 11:03
  • Updated then. I'm also not certain why you want to filter by `product_id`. – Bergi May 29 '21 at 11:11
  • Because i only want to find the quantity of that product sold to the customer – Shahal May 29 '21 at 11:13
  • ERROR: column "order_date" does not exist LINE 3: array_agg(product_qty ORDER BY order_date DESC) as product... – Shahal May 29 '21 at 11:17