SQL Datepart, multiply columns for a weekly sum

I have found that I have to use DATEPARTif I want to view data in a weekly manner, I'm just not sure how to put my SQL query together.

So far I,ve got this, which is the general idea.

var querythis = "SELECT DATEPART(wk, (kg * sett * rep)) as weeklyvol FROM Test GROUP BY date, kg, sett, rep";

So, kg, sett and rep are 3 of my database columns, also have date which of course is datetime, those 3 are all integers so I want to multiply them to a weekly total.

For example:

24 Jan 2017 - 100 x 10 x 3 = 3000
25 Jan 2017 - 100 x 5 x 5 = 2500
26 Jan 2017 - 150 x 3 x 3 = 1350

Which would result in Week 4 = 6850

GROUP BY date, kg, sett, rep";

Im not actually sure what the above row^ part of the code does, I had to include all of them or I'd get errors.

Currently its error free when I write it out like the following but it does not give me the result I want, I dont even know what the numbers come from, but at least not correct.

foreach (var c in db.Query(querythis))
{
    <a>@c.weeklyvol</a><br />
}

Anyone know how to do this?

I'm using SQL Server Compact if that makes a difference.

Solution

You're looking for something like this:

SELECT 
  DATEPART(wk, date),
  sum(kg * sett * rep) as weeklyvol 
FROM 
  Test 
GROUP BY 
  DATEPART(wk, date)

This will take the week from your date, and group by with it and also show the volume sum as the second column. You might want to use isowk instead of wk in datepart.

If you need also the year, using datepart(year... does not work properly, see for example this link. Without it dates on week 52, 53 and 1 can end up having wrong year number.