I got table like this where I want to calculate "WeekCount" so that number of "Done" is counted but grouped by Cycle and a Week. |
| ID | Day | Cycle | Week | Done |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 1 | 0 |
| 1 | 2 | 2 | 1 | 1 |
| 1 | 3 | 2 | 1 | 0 |
| 1 | 4 | 2 | 1 | 0 |
| 1 | 5 | 2 | 1 | 0 |
| 1 | 6 | 2 | 1 | 0 |
| 1 | 7 | 2 | 1 | 0 |
| 1 | 8 | 2 | 2 | 0 |
| 1 | 9 | 2 | 2 | 0 |
I've tried something like this:
SELECT a.ID, COUNT (a.Done) as Count_Done,a.Cycle,a.Week
FROM #Table a
JOIN #Table b ON a.ID=b.ID AND a.Cycle=b.Cycle AND a.Week=b.Week
WHERE a.Done=1
GROUP BY a.ID,a.Cycle,a.Week
but I am getting count of all rows , not just those where "Done=1" like this:
| ID | Count_Done | Cycle | Week |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 1 | 7 | 2 | 1 |
while I am expecting result like this:
| ID | Count_Done | Cycle | Week |
|---|---|---|---|
| 1 | 1 | 1 | 1 |
| 1 | 1 | 2 | 1 |
Thanks for your help!
CodePudding user response：
It's not clear why you need to join the table a second time at all.
I suspect the following will give you what you want. Since it's not joining the table a second time, the only rows it will see are the ones with Done=1 so the counts will be correct:
SELECT a.ID, COUNT (a.Done) as Count_Done,a.Cycle,a.Week
FROM #Table a
WHERE a.Done=1
GROUP BY a.ID,a.Cycle,a.Week
If you want to include id/cycle/week combinations that have the count as 0, I suggest using SUM instead of COUNT (if your done values are truly 0/1):
SELECT a.ID, SUM(a.Done) as Count_Done,a.Cycle,a.Week
FROM #Table a
GROUP BY a.ID,a.Cycle,a.Week
Or maybe
SELECT a.ID, COUNT(CASE WHEN a.Done=1 THEN 1 END) as Count_Done,a.Cycle,a.Week
FROM #Table a
GROUP BY a.ID,a.Cycle,a.Week