none
How to find average from pivot table in sql server?

    질문

  •         

    > I am trying to capture transactions per hour between given ranges. And
    > i need to identify the last one week of average transactions. here i
    > am facing some issue.

            Below is my trail:
            [Table Structure]
            [Current Result]
            [Query]
        
            SELECT * FROM (SELECT CONVERT(DATE, TimeStamp) AS [Date],DATEPART(hour,TimeStamp) AS [Hour],sum(CASE WHEN Result = 'F' THEN 1    ELSE 0    END) AS FAIL FROM TableName where  TimeStamp between '2018-05-12 00:00:00' and '2018-05-24 23:00:00'GROUP BY CONVERT(DATE, TimeStamp),DATEPART(hour,TimeStamp)) AS HourlyData PIVOT( SUM(FAILS) FOR [Hour] IN (    [8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])) AS DatePivot
        
             -- avg([Hour]  ) over(partition by DATEPART(hour,TimeStamp)) Avg_Item
        
        [Table strcture,Current result and expected result][1]
        
        Any body please correct me how to find average ? Tried with Over clause but not sure which coloumn i need to add in the script.
          [1]: https://i.stack.imgur.com/DeQYY.jpg
    Table structure, current result and expected output

    Chaitanya

    2018년 5월 17일 목요일 오전 6:21

모든 응답

  • see

    https://visakhm.blogspot.ae/2012/04/display-total-rows-with-pivotting-in-t.html

    you need to use a logic like this

     SELECT COALESCE(NULLIF(FORMAT([Date],'dd MMM yyyy'),'31 Dec 9999'),'Avg'), [8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23]
    FROM (
    SELECT CONVERT(DATE, TimeStamp) AS [Date],
    DATEPART(hour,TimeStamp) AS [Hour],
    sum(CASE WHEN Result = 'F' THEN 1    ELSE 0    END) AS FAIL 
    FROM TableName 
    where  TimeStamp between '2018-05-12 00:00:00' and '2018-05-24 23:00:00'
    GROUP BY CONVERT(DATE, TimeStamp),DATEPART(hour,TimeStamp)
    
    UNION ALL
    
    SELECT '99991231' AS [Date],
    DATEPART(hour,TimeStamp) AS [Hour],
    SUM(CASE WHEN Result = 'F' THEN 1.0    ELSE 0.0    END)/COUNT(DISTINCT CONVERT(DATE, TimeStamp)) AS FAIL 
    FROM  TableName 
    where  TimeStamp between '2018-05-12 00:00:00' and '2018-05-24 23:00:00'
    GROUP BY DATEPART(hour,TimeStamp)
    ) AS HourlyData 
    PIVOT( SUM(FAILS) 
    FOR [Hour] IN 
    (    [8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])) AS DatePivot
    ORDER BY [Date]


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • 편집됨 Visakh16MVP 2018년 5월 17일 목요일 오전 6:49
    2018년 5월 17일 목요일 오전 6:48
  • Hi Vikash

    I am getting error invalid coloumn name fails, could you please cross check and kindly let me the know the logic behind it? 

     SELECT COALESCE(NULLIF(FORMAT([Date],'dd MMM yyyy'),'31 Dec 9999'),'Avg'), [8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23]
    
    SELECT '99991231' AS [Date],
    

    PIVOT( SUM(FAILS)  -- Here query is throwing error
    



    CPK


    2018년 5월 17일 목요일 오전 6:57
  • Hi Vikash

    I am getting error invalid coloumn name fails, could you please cross check and kindly let me the know the logic behind it? 

     SELECT COALESCE(NULLIF(FORMAT([Date],'dd MMM yyyy'),'31 Dec 9999'),'Avg'), [8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23]
    SELECT '99991231' AS [Date],

    PIVOT( SUM(FAILS)  -- Here query is throwing error



    CPK


    Hi Chaitanya,

    How about this?

    ;with cte as
    (
    SELECT CONVERT(VARCHAR(10),[Date],105) AS [Date],[8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], 1 AS V_LEVEL 
    FROM 
    (
    SELECT CONVERT(DATE, TimeStamp) AS [Date],
    DATEPART(hour,TimeStamp) AS [Hour],
    sum(CASE WHEN Result = 'F' THEN 1    ELSE 0    END) AS FAIL 
    FROM TableName where  TimeStamp between '2018-05-12 00:00:00' and '2018-05-24 23:00:00'
    GROUP BY CONVERT(DATE, TimeStamp),DATEPART(hour,TimeStamp) 
    ) AS HourlyData 
    PIVOT( 
           SUM(FAIL) FOR [Hour] IN (    [8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])
    	 ) AS DatePivot
    
    union all
    
    SELECT 
        'Avg', [8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23],2 AS V_LEVEL
    
    FROM 
    (
    SELECT 
    DATEPART(hour,TimeStamp) AS [Hour],
    AVG(CASE WHEN Result = 'F' THEN 1    ELSE 0    END)  AS AVG_FAIL 
    FROM TableName where  TimeStamp between '2018-05-12 00:00:00' and '2018-05-24 23:00:00'
    GROUP BY DATEPART(hour,TimeStamp) 
    ) AS HourlyData 
    PIVOT( 
           MAX(AVG_FAIL) FOR [Hour] IN (    [8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])
    	 ) AS DatePivot
    )
    SELECT 
    [Date],[8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23]
    FROM cte
    order by V_LEVEL

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 5월 18일 금요일 오전 9:43
    중재자
  • Hi Vikash

    I am getting error invalid coloumn name fails, could you please cross check and kindly let me the know the logic behind it? 

     SELECT COALESCE(NULLIF(FORMAT([Date],'dd MMM yyyy'),'31 Dec 9999'),'Avg'), [8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23]
    
    SELECT '99991231' AS [Date],
    

    PIVOT( SUM(FAILS)  -- Here query is throwing error
    



    CPK


    it should be fail

    SELECT COALESCE(NULLIF(FORMAT([Date],'dd MMM yyyy'),'31 Dec 9999'),'Avg'), [8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23]
    FROM (
    SELECT CONVERT(DATE, TimeStamp) AS [Date],
    DATEPART(hour,TimeStamp) AS [Hour],
    sum(CASE WHEN Result = 'F' THEN 1    ELSE 0    END) AS [FAIL] 
    FROM TableName 
    where  TimeStamp between '2018-05-12 00:00:00' and '2018-05-24 23:00:00'
    GROUP BY CONVERT(DATE, TimeStamp),DATEPART(hour,TimeStamp)
    
    UNION ALL
    
    SELECT '99991231' AS [Date],
    DATEPART(hour,TimeStamp) AS [Hour],
    SUM(CASE WHEN Result = 'F' THEN 1.0    ELSE 0.0    END)/COUNT(DISTINCT CONVERT(DATE, TimeStamp)) AS [FAIL] 
    FROM  TableName 
    where  TimeStamp between '2018-05-12 00:00:00' and '2018-05-24 23:00:00'
    GROUP BY DATEPART(hour,TimeStamp)
    ) AS HourlyData 
    PIVOT( SUM([FAIL]) 
    FOR [Hour] IN 
    (    [8], [9], [10],[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])) AS DatePivot
    ORDER BY [Date]


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오전 9:50