当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - Using Rollup with SQL produces double results

问题描述:

I am running a query to try and obtain some metrics by day of week as well as a total number. I am using ROLLUP to obtain a total average for the week. However, when I do so I see that the results are doubled. The numbers are the same and I verified that they are correct. However, I would like to have it produce only Sunday-Saturday with a Total listed below. Is there a way to re-write this query to do so?

select DATEPART(dw,t1.Call), DATENAME(dw,t1.Call),

AVG (T1.CallSec + T1.ACWSec) AS AHT

from bm.calls t1 with (nolock)

JOIN prc.RRequest rreq

on t1.callid = rreq.t1CallID

join PRC.RRaw rraw with (nolock)

on rreq.RoutingID = rraw.RoutingId

where t1.CallDate >= '2014-01-01'

AND rraw.RMode='Active'

GROUP BY ROLLUP (DATEPART(dw,t1.Call)),(DATENAME(dw,t1.Call) )

Here is the output that I get

NULL Monday 367.70605696962

NULL Sunday 335.661825117371

NULL Tuesday 359.261558682542

NULL NULL 353.421450966283

NULL Friday 361.478009630819

NULL Saturday 354.216226822329

NULL Thursday 330.073051380636

NULL Wednesday 345.357746478873

1 Sunday 335.661825117371

2 Monday 367.70605696962

3 Tuesday 359.261558682542

4 Wednesday 345.357746478873

5 Thursday 330.073051380636

6 Friday 361.478009630819

7 Saturday 354.216226822329

Desired Output should be

1 Sunday 335.661825117371

2 Monday 367.70605696962

3 Tuesday 359.261558682542

4 Wednesday 345.357746478873

5 Thursday 330.073051380636

6 Friday 361.478009630819

7 Saturday 354.216226822329

NULL NULL 353.421450966283

网友答案:

I believe you just need to change your ROLLUP to contain both fields:

SELECT  DATEPART(dw, t1.Call)
      , DATENAME(dw, t1.Call)
      , AVG(T1.CallSec + T1.ACWSec) AS AHT
FROM    bm.calls t1 WITH ( NOLOCK )
        JOIN prc.RRequest rreq ON t1.callid = rreq.t1CallID
        JOIN PRC.RRaw rraw WITH ( NOLOCK ) ON rreq.RoutingID = rraw.RoutingId
WHERE   t1.CallDate >= '2014-01-01'
        AND rraw.RMode = 'Active'
GROUP BY ROLLUP((DATEPART(dw, t1.Call), DATENAME(dw, t1.Call)))

ROLLUP() will add subtotal/total lines based on sets of fields included, you can combine multiple fields into one set by wrapping them in parenthesis.

So ROLLUP(col1,col2) will result in subtotals for each change in col2 in addition to a grand total., while ROLLUP((col1,col2)) will combine both columns into one set, and therefore show just a grand total.

分享给朋友:
您可能感兴趣的文章:
随机阅读: