How to fetch data from same column using different criteria?

Question

Hi I have table like this in SQL anywhere database

CUSTID-------DDAT.----------AMOUNT
1.         01-01-2021.       1000
1.         02-02-2021.       2000
1.         03-02-2021.       3000
1.         04-02-2021.       4000
2.         01-04-2021.        1000
2.         02-04-2021.         2000

```
         04-04-2021.         1000
```

I want data like this in VB.net where amount is only for one date and total amount is for 4 day

Cust id.------date ---------------Amount.-------Total amount
1.             04-04-2021.          4000.                 10000
 2.             04-04-2021.          1000.                 4000

Can you give me any solution..thanks in advance

Do you know which date you want? Why doesn't the first column match the second? — Gordon Linoff, Aug 20 '21 at 12:06
*"I tried many SQL queries"*. If we can't see it then it didn't happen. Show us the one attempt that you think should work. That's how this site works. It's not *"I want to do X, show me how"*. It's *"I want to do X, this is how I'm trying, tell me how to fix it"*. — jmcilhinney, Aug 20 '21 at 12:10

score 0 · Answer 1 · answered Aug 20 '21 at 15:24

My take on it:

select custid, dat, amount, total_amount
from (
    select custid, dat, amount, sum(amount) over (partition by custid) as total_amount
    from data
) d
where dat = '2021-04-04' -- or any other date

Might be that the inner select is all that you need. Not sure if the filter on date is necessary.

How to fetch data from same column using different criteria?

1 Answers1