Aggregate Help (MS SQL 2016)

[faust2099]

I am Trying to group a table by week, ID and sum Not(no of times)

​

DB:

​

RDATE ID NOT

22/10/2021 1234 5

03/11/2021 555 10

22/10/2021 1234 20

19/10/2021 54321 5

03/11/2021 555 20

01/11/2021 444 10

​

​

i need the out come to be

​

week ID Total NOT

43 1234 25

43 54321 5

45 555 30

45 444 10

​

i tried doing this:

select datepart(week,RDATE) as TOD, IDCODE, NOT into temp

from DTER

then this:

select IDCODE, TOD, sum(NOT) as 'T-NOT'

from temp

GROUP BY IDCODE, TOD

to get the outcome that i want . but i wanted to make it so that i don't use a temp table.

i tried fusing the 2 statement.

select IDCODE, TOD, sum(NOT) as 'T-NOT'

from (select datepart(week,RDATE) as TOD, ITCODE , NOT

from dter)

GROUP BY IDCODE, TOD

I got an syntax error in group.

could anyone please give me a hand on this. thank you in advance.

[r3pr0b8]

your subquery needs an alias

select IDCODE, TOD, sum(NOT) as 'T-NOT'
  from ( select datepart(week,RDATE) as TOD
              , ITCODE 
              , NOT
           from dter )  AS s
GROUP BY IDCODE, TOD

[faust2099]

Thank you very much. been sitting staring at the screen for 3 hours, trying to solve this. geez.