Need to get number of Items used in last 3 days as well as used items in last year

user_16006980 0
Viewed 34

I have the query where I can fetch last 3 days records perfectly

Select ProductID, Description, Count(productID) AS [Last 3 days item count]

From VIEW_ItemsUsed

Where DateUsed >= Getdate()-3

Group by ProductID

But when I include the condition for fetching the number of items used in last year in the same query, getting invalid data

Select ProductID, Description, Count(productID) AS [Last 3 days item count], Count(ProductID) AS [Last Year Item counts]

From VIEW_ItemsUsed

Where DateUsed >= Getdate()-3
      AND DateUsed >= Getdate()-365

Group by ProductID

Please provide me the solution

sql
1 Answers

I think you just want conditional aggregation:

Select ProductID, Description,
       sum(case when dateUsed >= getdate() - 3 then 1 else 0 end) as cnt_3days,
       sum(case when dateUsed >= getdate() - 365 then 1 else 0 end) as cnt_365days
From VIEW_ItemsUsed
Where DateUsed >= Getdate() - 365
Group by ProductID, Description;
user_11440350
Related