December 17, 2017 at 8:01 pm
I have table name sample and has below records. The Result I am expecting is mentioned below to this table.
I want count of ( stockid X Lineitemitd ) combination which is >=1
Sample
| id | stockid | Category | lineitemid |
| 10 | 2 | debits | 1 |
| 11 | 4 | debits | 1 |
| 12 | 5 | debits | 1 |
| 7 | 2 | deposits | 1 |
| 8 | 4 | deposits | 1 |
| 9 | 5 | deposits | 1 |
| 5 | 4 | sales | 1 |
| 6 | 5 | sales | 1 |
| 1 | 2 | sales | 1 |
| 2 | 2 | purchases | 21 |
| 3 | 4 | purchases | 21 |
| 4 | 5 | purchases | 21 |
| 13 | 2 | other | 26 |
| 14 | 4 | other | 26 |
| 15 | 5 | other | 26 |
Result Expected:
| id | stockid | Category | lineitemid | Count |
| 10 | 2 | debits | 1 | 3 |
| 11 | 4 | debits | 1 | 3 |
| 12 | 5 | debits | 1 | 3 |
| 7 | 2 | deposits | 1 | 3 |
| 8 | 4 | deposits | 1 | 3 |
| 9 | 5 | deposits | 1 | 3 |
| 5 | 4 | sales | 1 | 3 |
| 6 | 5 | sales | 1 | 3 |
| 1 | 2 | sales | 1 | 3 |
| 2 | 2 | purchases | 21 | 1 |
| 3 | 4 | purchases | 21 | 1 |
| 4 | 5 | purchases | 21 | 1 |
| 13 | 2 | other | 26 | 1 |
| 14 | 4 | other | 26 | 1 |
| 15 | 5 | other | 26 | 1 |
December 18, 2017 at 7:57 am
You just use both columns in your PARTITION BY clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 18, 2017 at 8:23 pm
Hi Drew,
Thanks for the reply. I will try that.
December 18, 2017 at 10:32 pm
Can you kindly elaborate how you got count=3 and count=1?
Saravanan
Saravanan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply