none
Query help for consolidated count RRS feed

  • Question

  • Hi experts,

    Here is the query i tried to get hour wise count of all transaction

    Now i want to sum a total count in end of the column

    SELECT *
    FROM (SELECT CAST(txn_dt AS DATE) [Date], 
       DATEPART(hour,txn_dt) [Hour], Count(1)  [Txn Count]   
       FROM TXN_TB
     where CONVERT (DATE,txn_dt)        = CONVERT (date, GETDATE())
       GROUP BY CAST(txn_dt AS DATE), 
             DATEPART(hour,txn_dt)) AS HourlytxnData
    PIVOT( SUM([Txn Count])   
      FOR [Hour] IN ([0], [1], [2], [3], [4], [5], [6], [7],
         [8], [9], [10],[11], [12], [13], [14], [15], [16], 
         [17], [18], [19], [20], [21], [22], [23])) AS DatePivot

    date           0   1   2  ..  Total

    15-07-19     5   8   4     17

    16-07-19            4   12   7        23

    please help

    Tuesday, July 16, 2019 9:00 AM

All replies

  • Import the result into a #tmp and then perform calculation

    SELECT *
    INTO #tmp FROM (SELECT CAST(txn_dt AS DATE) [Date], 
       DATEPART(hour,txn_dt) [Hour], Count(1)  [Txn Count]   
       FROM TXN_TB
     where CONVERT (DATE,txn_dt)        = CONVERT (date, GETDATE())
       GROUP BY CAST(txn_dt AS DATE), 
             DATEPART(hour,txn_dt)) AS HourlytxnData
    PIVOT( SUM([Txn Count])   
      FOR [Hour] IN ([0], [1], [2], [3], [4], [5], [6], [7],
         [8], [9], [10],[11], [12], [13], [14], [15], [16], 
         [17], [18], [19], [20], [21], [22], [23])) AS DatePivot

    SELECT *
    ,      (   SELECT  SUM(val) 
               FROM    (VALUES (0)
                           ,   (1)
                           ,   (2)
       ,   (3)
       ,   (4)
        ...
    .....
                       ) AS value(val)
           ) AS Total 
    FROM #tmp ;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 16, 2019 9:15 AM
    Answerer
  • Hi 

    After insert this result to temp table run following script

     
     declare @script nvarchar(max)
    
    set @script=
    (
    select 'select [date],' + 
    (Select  STUFF((
                SELECT '],[' + NAME 
     From  Tempdb.Sys.Columns Where Object_ID = Object_ID('tempdb..#TempTable') and name<>'date'
    
     FOR XML PATH ('') ), 1, 2, '') ) + '],'+
    (Select  STUFF((
                SELECT ']+[' + NAME 
     From  Tempdb.Sys.Columns Where Object_ID = Object_ID('tempdb..#TempTable') and name<>'date'
    
     FOR XML PATH ('') ), 1, 2, '') ) + '] as total from  #TempTable' )
     
    exec sp_executesql @script

    Tuesday, July 16, 2019 12:22 PM
  •  SELECT [Date],
     [0], [1], [2], [3], [4], [5], [6], [7],
         [8], [9], [10],[11], [12], [13], [14], [15], [16], 
         [17], [18], [19], [20], [21], [22], [23],  [99] as 'Total'
    FROM (
     SELECT CAST(txn_dt AS DATE) [Date], 
      ISNULL( DATEPART(hour,txn_dt),99) [Hour], Count(*)  [Txn Count]   
       FROM TXN_TB
     where CONVERT (DATE,txn_dt) = CONVERT (date, GETDATE())
       GROUP BY 
       GROUPING SETS ((CAST(txn_dt AS DATE), DATEPART(hour,txn_dt) ),(CAST(txn_dt AS DATE)))
       ) AS HourlytxnData
       PIVOT( SUM([Txn Count])   
      FOR [Hour] IN ([0], [1], [2], [3], [4], [5], [6], [7],
         [8], [9], [10],[11], [12], [13], [14], [15], [16], 
         [17], [18], [19], [20], [21], [22], [23]
    	 ,[99])) AS DatePivot
    

    Tuesday, July 16, 2019 1:55 PM
    Moderator
  • Hi Antonioy,

     

    Here's the code you can refer to:

    IF OBJECT_ID('TXN_TB') IS NOT NULL drop table TXN_TB
    create table TXN_TB ( 
    [txn_dt] varchar(50))
    go
    insert into TXN_TB values
    ('2019-07-17 00:12:07.717'),
    ('2019-07-17 00:15:07.222'),
    ('2019-07-17 01:12:07.717'),
    ('2019-07-17 01:22:07.717'),
    ('2019-07-17 01:44:07.717'),
    ('2019-07-17 01:55:07.717'),
    ('2019-07-17 02:12:07.717'),
    ('2019-07-17 02:46:07.717'),
    ('2019-07-17 05:45:07.717'),
    ('2019-07-17 08:45:07.717')
    
    
    ;with cte as(
    SELECT *
    FROM (SELECT CAST(txn_dt AS DATE) [Date], 
       DATEPART(hour,txn_dt) [Hour], Count(1)  [Txn Count]   
       FROM TXN_TB
     where CONVERT (DATE,txn_dt)= CONVERT (date, GETDATE())
       GROUP BY CAST(txn_dt AS DATE), 
             DATEPART(hour,txn_dt)) AS HourlytxnData
    PIVOT( SUM([Txn Count])   
      FOR [Hour] IN ([0], [1], [2], [3], [4], [5], [6], [7],
         [8], [9], [10],[11], [12], [13], [14], [15], [16], 
         [17], [18], [19], [20], [21], [22], [23])) AS DatePivot
    )
    
    select *, ISNULL([0],0)+ isnull([1],0)+ isnull ([2],0)+ isnull ([3],0)+isnull ([4],0)+
    isnull ([5],0)+isnull ([6],0)+isnull ([7],0)+isnull ([8],0)+isnull ([9],0)+isnull ([10],0)+
    isnull ([11],0)+isnull ([12],0)+isnull ([13],0)+isnull ([14],0)+isnull ([15],0)+isnull ([16],0)+
    isnull ([17],0)+isnull ([18],0)+isnull ([19],0)+isnull ([20],0)+isnull ([21],0)+isnull ([22],0)+
    isnull ([23],0)as total
    from cte
    
    /*
    Date       0           1           2           3          ... 22          23          total
    ---------- ----------- ----------- ----------- -----------... ----------- ----------- -----------
    2019-07-17 2           4           2           NULL       ... NULL        NULL        10
    */

    Regards,

    Sabrina


    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.

    Wednesday, July 17, 2019 7:18 AM