Production amount each year by ID and Date

I would like to get yearly production growth amount based on ID and DateTime. In the above example, for the ID – 1, it is showing perfect (705+326=1031). But for the ID – 104, it is not showing right answer like (10+54=64). where is the wrong in my SQL query below to get the annual production growth amount by Date and ID?

select top 100 ID, Date, count(productionID),  sum(count(productionID)) over (order by date) as 'Annual_Production'
from Production_Table
where ID in (1, 104)
group by ID, Date
order by ID, Date desc;

Answer

You need to PARTITION BY ID in your windowed function.

Advertisement

Answer