SQL Server : how to group by value, multiple colum

问题:

I couldn't find a solution for this and also my SQL skills are very poor, so maybe someone can advise if the following is possible.

Environment: Microsoft SQL Server 2008 R2

The current query is very simple, selecting the values of error codes, counting the number of errors and grouping on the error codes:

• EC - ErrorCode (there are arround 200 different error codes)
• NoEr - count(*) as NoEr (some days the error count can be empty)

Query:

SELECT 
    ErrorCode AS EC, COUNT(*) as NoEr 
FROM 
    [DB].[dbo].[Table] 
WHERE
    ERRORTIME > '2018-12-30 00:00:00' 
    AND ERRORTIME < '2018-12-30 23:59:59' 
GROUP BY
    errorcode 
ORDER BY 
    ERRORCODE ASC

Output:

+----+-------+
| EC |  NoEr |  
+----+-------+
| A9 |  3333 | 
| E0 |  1505 |
| G9 |  1233 | 
| X1 |  2    | 
+----+-------+

I would like to obtain the count of errors for the last 5 days displayed as per below:

+----+-------+-------+-------+-------+-------+
| EC |  MON  |  TUE  |  WED  |  THU  |  FRI  |
+----+-------+-------+-------+-------+-------+
| A9 |  1505 |  2333 |       |  1555 |  9999 |
| E0 |  3333 |       |  2311 |  5555 |  14   |
| G9 |  2222 |  1505 |   123 |  1233 |       |
| X1 |  1212 |  1233 |  1155 |  3    |       |
+----+-------+-------+-------+-------+-------+

Thank you & have a great new year !

回答1:

You can us CASE expression along with aggregate SUM to calculate the count for each weekday like below -

Select 
    ErrorCode as EC,
    SUM(CASE WHEN Datename(w, ErrorTime) = 'Monday' THEN 1 ELSE 0 END) as MON,
    SUM(CASE WHEN Datename(w, ErrorTime) = 'Tuesday' THEN 1 ELSE 0 END) as TUE,
    SUM(CASE WHEN Datename(w, ErrorTime) = 'Wednesday' THEN 1 ELSE 0 END) as WED,
    SUM(CASE WHEN Datename(w, ErrorTime) = 'Thursday' THEN 1 ELSE 0 END) as THU,
    SUM(CASE WHEN Datename(w, ErrorTime) = 'Friday' THEN 1 ELSE 0 END) as FRI
From
    [DB].[dbo].[Table]
where 
    ERRORTIME > '2018-12-30 00:00:00' and ERRORTIME < '2018-12-30 23:59:59' 
group by errorcode 
order by ERRORCODE ASC

回答2:

You could use pivot operator to achieve your goal.

SELECT 
EC,[MON],[TUE],[WED],[THU],[FRI]
FROM 
(
SELECT 
ErrorCode AS EC,UPPER(LEFT(DATENAME(DW,ERRORTIME),3)) AS D_W, 1 AS NUMBER FROM [DB].[dbo].[Table]
WHERE DATEDIFF(DAY,ERRORTIME,GETDATE())<=5
) SRC
PIVOT
(
SUM(NUMBER) FOR D_W IN ([MON],[TUE],[WED],[THU],[FRI])
) PVT

Best Regards,

Will

回答3:

Like Will he said the best option is to use the PIVOT:

SELECT 
    *
FROM
(
    SELECT 
        ErrorCode AS                               EC, 
        UPPER(LEFT(DATENAME(DW, ERRORTIME), 3)) AS DayW, 
        1 AS                                   Number
    FROM 
         table_name
    WHERE
                ERRORTIME > = DATEADD(day, -5, GetDate())
) AS Source PIVOT(SUM(Number) FOR DayW IN(
    [MON], 
    [TUE], 
    [WED], 
    [THU], 
    [FRI])) PVT

回答4:

You want the results for the last five days, not for a single day, so I think you want:

SELECT ErrorCode as EC,
       SUM(CASE WHEN datename(weekday, ErrorTime) = 'Monday' THEN 1 ELSE 0 END) as MON,
       SUM(CASE WHEN datename(weekday, ErrorTime) = 'Tuesday' THEN 1 ELSE 0 END) as TUE,
       SUM(CASE WHEN datename(weekday, ErrorTime) = 'Wednesday' THEN 1 ELSE 0 END) as WED,
       SUM(CASE WHEN datename(weekday, ErrorTime) = 'Thursday' THEN 1 ELSE 0 END) as THU,
       SUM(CASE WHEN datename(weekday, ErrorTime) = 'Friday' THEN 1 ELSE 0 END) as FRI
FROM [DB].[dbo].[Table] t
WHERE ERROR_TIME >= DATEADD(DAY, -5, CAST(GETDATE() as DATE)) AND
      ERROR_TIME >= CAST(GETDATE() as DATE)
GROUP BY errorcode 
ORDER BY ERRORCODE ASC;

Note that when you use functions such as DATEADD() and DATENAME() you should not use abbreviations. That is just a bad query-writing habit. No one want to have to remember whether w means "week" or "weekday" or whether m means "minute" or "month". Use the full name and eschew ambiguity.

If you like you can also simplify this to:

SELECT ErrorCode as EC,
       SUM(CASE WHEN ErrorTime_weekday = 'Monday' THEN 1 ELSE 0 END) as MON,
       SUM(CASE WHEN ErrorTime_weekday = 'Tuesday' THEN 1 ELSE 0 END) as TUE,
       SUM(CASE WHEN ErrorTime_weekday = 'Wednesday' THEN 1 ELSE 0 END) as WED,
       SUM(CASE WHEN ErrorTime_weekday = 'Thursday' THEN 1 ELSE 0 END) as THU,
       SUM(CASE WHEN ErrorTime_weekday = 'Friday' THEN 1 ELSE 0 END) as FRI
FROM [DB].[dbo].[Table] t CROSS APPLY
     (VALUES (datename(weekday, ErrorTime) = 'Thursday' THEN 1 ELSE 0 END))
     ) v(ErrorTime_weekday)
WHERE ERROR_TIME >= DATEADD(DAY, -5, CAST(GETDATE() as DATE)) AND
      ERROR_TIME >= CAST(GETDATE() as DATE)
GROUP BY errorcode 
ORDER BY ERRORCODE ASC;