0

In Postgresql I have table of items as follow:

id qty 1 20 2 45 3 10

it contains the quantity of each product. I'm doing a counting operation. For every item I count I copy the data to a a log_count table. Items table NEVER CHANGED.

I want to write a query which will show me the difference between the tables. Basically how much left to pass over. When the quantity is 0 the row disappears.

This is how it should be:

Start:

items: log_count: QUERY SHOW: 1 20 1 20 2 45 2 45 3 10 3 10

after doing count of: id=1 qty=3

items: log_count: QUERY SHOW: 1 20 1 3 1 17 2 45 2 45 3 10 3 10

later, after doing count of: id=2 qty=45

items: log_count: QUERY SHOW: 1 20 1 3 1 17 2 45 2 45 3 10 3 10 * row of id 2 is gone as its qty=0

later, after doing count of: id=1 qty=2

items: log_count: QUERY SHOW: 1 20 1 5 1 15 2 45 2 45 3 10 3 10

Final stage...

items: log_count: QUERY SHOW: 1 20 1 20 2 45 2 45 3 10 3 10

How do I write this query?

Improve this question

1 Answer 1

Reset to default
1

Looks like a simple join to me:

select it.id, it.qty - lc.qty as difference from items it left join log_count lc on it.id = lc.id where it.qty - lc.qty > 0;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

can you explain why left join?
Because your fourth sample data has no row for id=3 in log_count but you still want to see it in the output. If the query was using an inner join, only rows would show up that do have a row in log_count
but the join is with items . there can not be id in log_count which doesn't exists in items....
Then your sample data is wrong. Only in the last sample data, you have the same number of rows in both tables. All other examples have fewer rows in log_count than items. If you are certain that all ids from items are always present in log_count then obviously the outer join is not needed.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.