CODEWITHSUNDEEP
sqlpostgresql
John
John

Reputation: 1852

Is it possible to create and use window function in the same query?

I'm using PostgreSQL and I have the following situation:

table of Sales (short version):

itemid quantity
  5      10
  5      12
  6       1

table of stock (short version):

itemid stock
5        30
6         1

I have a complex query that also needs to present in one of it's columns the SUM of each itemid.

So it's going to be:

Select other things,itemid,stock, SUM (quantity) OVER (PARTITION BY itemid) AS total_sales
from .....
     sales
     stock

This query is OK. however this query will present:

itemid stock total_sales
  5       30     22
  6       1       1

But I don't need to see itemid=6 because the whole stock was sold. meaning that I need a WHERE condition like:

WHERE total_sales<stock

but I can't do that as the total_sales is created after the WHERE is done.

Is there a way to solve this without surrounding the whole query with another one? I'm trying to avoid it if I can.

Upvotes: 1

Views: 72

Answers (2)

shA.t
shA.t

Reputation: 16958

You can also use an inner select in your WHERE statement like this:

SELECT *, SUM (quantity) OVER (PARTITION BY itemid) AS total_sales
FROM t
WHERE quantity <> (SELECT SUM(quantity) FROM t ti WHERE t.itemid = ti.itemid);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can use a subquery or CTE:

select s.*
from (Select other things,itemid,stock,
             SUM(quantity) OVER (PARTITION BY itemid) AS total_sales
      from .....
     ) s
where total_sales < stock;

You cannot use table aliases defined in a SELECT in the SELECT, WHERE, or FROM clauses for that SELECT. However, a subquery or CTE gets around this restriction.

Upvotes: 2

Related Questions