4

Table

Table

Could someone assist I am looking for a solution to get the counts correct for each store item pair. The first count is easy

COUNT (*) OVER(PARTITION BY store ORDER BY s.deptitemcode DESC) StoreItemSeqNo

However for the 2nd count, I only want the count if the Flag is true for each store item pair if the flag is false then the count should be the previous value if there was no previous value it should be zero.

Refer to table example

Improve this question
4
  • Post actual in text and desired results. You requirement is not clear to me.
    – paparazzo
    Commented Mar 2, 2018 at 14:15
  • Which DBMS product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product.
    – user330315
    Commented Mar 2, 2018 at 14:17
  • What do you mean with "get the counts correct". What do you consider a "correct" count? You should edit your question and add some sample data and the expected output based on that data as formatted text please, no screen shots.
    – user330315
    Commented Mar 2, 2018 at 14:18
  • Maybe the first count is easy but pretty sure it is wrong. Did you test this? Is this homework?
    – paparazzo
    Commented Mar 2, 2018 at 14:20

1 Answer 1

Reset to default
12

Aggregate functions can instead of * also take an expression. If COUNT(..) is called for an expression and not *, it counts everything except NULL. So, to count according to flag:

COUNT (CASE WHEN flag = 1 THEN flag ELSE NULL END) OVER(PARTITION BY store ORDER BY s.deptitemcode DESC) StoreItemSeqNo
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.