Group by Week with SQL

Select SQL results grouped by weeks

I think this should do it..

Select 
ProductName,
WeekNumber,
sum(sale)
from
(
    SELECT 
    ProductName,
    DATEDIFF(week, '2011-05-30', date) AS WeekNumber,
    sale
    FROM table
)
GROUP BY
ProductName,
WeekNumber

GROUP BY WEEK with SQL

You can use DATEPART(), this groups by both the week and the year in the event you have data spanning multiple years:

SELECT 
    'Week ' + cast(datepart(wk, created) as varchar(2)) Week,
    SUM(case WHEN status = 1 then 1 else 0 end) Status1,
    SUM(case WHEN status = 2 then 1 else 0 end) Status2,
    SUM(case WHEN status = 3 then 1 else 0 end) Status3,
    SUM(case WHEN status = 4 then 1 else 0 end) Status4,
    SUM(case WHEN status = 5 then 1 else 0 end) Status5
FROM contacts
group by datepart(wk, created), year(created)

See SQL Fiddle with Demo

Adding the year to the final result:

SELECT 
    'Week ' + cast(datepart(wk, created) as varchar(2)) Week,
    year(created) year,
    SUM(case WHEN status = 1 then 1 else 0 end) Status1,
    SUM(case WHEN status = 2 then 1 else 0 end) Status2,
    SUM(case WHEN status = 3 then 1 else 0 end) Status3,
    SUM(case WHEN status = 4 then 1 else 0 end) Status4,
    SUM(case WHEN status = 5 then 1 else 0 end) Status5
FROM contacts
group by  datepart(wk, created), year(created)

See SQL Fiddle with demo

Report - SQL group by week number and year

From what I understand from your question, you want to dynamically search by weeknumber, mac_address and year returning the highest temperature for each day matching in that range. A query like the following should do what you are after.

declare @macaddress varchar(255) = '2', @WeekNumber int = 36, @year int = 2019

Select 
  Date = tm,
  Temperature = max(temperature)
from 
  Temps t
where 
  year(tm)=@year 
  and mac_address=@macaddress 
  and datepart(week,tm) = @WeekNumber
 group by
   tm

If you want the mac_address included in the results, simply add to the Select and Group By sections.

Here's the SQL fiddle with a test setup.

SQL Group by Day of Week

You want aggregation:

SELECT SUM(CASE WHEN DATENAME(dw, MachineMidLineDate) = 'Sunday' THEN [Total Mins] END) AS Sun,
       SUM(CASE WHEN DATENAME(dw, MachineMidLineDate) = 'Monday' THEN [Total Mins] END) AS Mon,
      . . . 
FROM dbo.vw_Machine_Minutes_Overview;

---

Related Topics