postgresql complex group by in query

Question

I have a query that gives:

itemid deadlineneeded delievrydate quantity 
200  15/07/15        14/07/15     5     
200  15/07/15        14/07/15     10
200  15/07/15        13/07/15     25
201  15/07/15        14/07/15     30
200  14/07/15        10/07/15     3
201  15/07/15        15/07/15     100

It gives the information from multiple tables. Basically it means When items arrive to warehouse (delievrydate) and how many. The deadlineneeded means when i'm going to use it.

My goal is to find out the total quantity of itemid that arrives 1 day before deadlineeded.

for example with the last data I want to get:

itemid deadlineneeded    quantity
200         15/07/15      43 (5+10+25+3)  
200         14/07/15      3    //5+10+25 not included because deliverydate
201         15/07/15     30    //100 not included because deliverydate

How do i get it?

I tried. it gives false results... to solve it I must take under considration the delievrydate and it is something that changes for each row... it's not a simple sum. — user39722
– user39722, Commented Jun 23, 2015 at 8:51

Patrick · Accepted Answer · 2015-06-23 08:59:58Z

1

SELECT itemid, deadlineneeded, sum(quantity) AS total_quantity
FROM <your table>
WHERE (deadlineneeded - delievrydate)::int >= 1
GROUP BY 1, 2
ORDER BY 1, 2;

This uses a "delievrydate" (looks like a typo to me) that is at least 1 day before the "deadlineneeded" date, as your sample data is suggesting.

answered Jun 23, 2015 at 8:59

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vao Tsun · Accepted Answer · 2015-06-23 08:54:49Z

0

select distinct date_trunc('day',deadlineneeded),sum from 
(select *,sum(quantity) over (partition by deadlineneeded-delievrydate) from     tablename) t 
where deadlineneeded-delievrydate = '1 day';

answered Jun 23, 2015 at 8:54

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

1 Comment

Vao Tsun Over a year ago

you won't see itemid, cos you summarize the quantity... you can list them though comma though...

jarlh · Accepted Answer · 2015-06-23 08:59:54Z

0

Use GROUP BY combined with aggregate function SUM.

select itemid, deadlineneeded, SUM(quantity)
from tablename
where deadlineneeded = delievrydate + 1
group by itemid, deadlineneeded

The where deadlineneeded = delievrydate + 1 part is typically product specific, and I don't know postgresql... ANSI SQL has deadlineneeded = delievrydate + interval'1' day.

answered Jun 23, 2015 at 8:59

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

postgresql complex group by in query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related