GROUP BY WEEK with SQL

问题:

I have the following:

http://sqlfiddle.com/#!6/226ae/1

I'm trying to now add one row for each week of the year, and filter the contacts accordingly. CONTACTS has a datetime column. The new table will look like:

        Status 1    Status 2    Status 3
Week 1      3          4           2
Week 2      1          5           3
Week 3      2          2           4

I think that DATEADD needs to be used, however I'm at a loss in terms of how to begin changing my query.

I do know that MySQL has a GROUP BY WEEK command, but I don't think that SQL has an equivalent. What's the best way to accomplish this?

回答1:

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

回答2:

You can use the datepart function to extract the week from a date.

The query becomes:

SELECT datepart(week, created) as 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(week, created)

SqlFiddle: http://sqlfiddle.com/#!6/226ae/6tsq

回答3:

You might try using the group by clause in your query:

SELECT
    DATE_FORMAT( created, '%u' ) week_number,
    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 DATE_FORMAT( created, '%u' )

I'm assuming you are talking about mysql.

The DATE_FORMAT function is documented here:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format