SQL Query for a dashboard. How can I count how many times a From_Number has called a To_Number?

a) per Date? with desired output: [How_many_calls_to_num_per_day]
b) In general (across Dates)? with desired output: [How_many_calls_to_num]

SELECT tbl.*
FROM (
  VALUES

  ( '1/02/2020',    '21123456',  '27564564', '1',   '4'     )
  ,(    '1/02/2020',    '21123456',  '27564564', '1',   '4'     )
  ,(    '1/02/2020',    '21123456',  '21622222', '3',   '1'     )
  ,(    '1/02/2020',    '21633499',  '21636363', '3',   '3'     )
  ,(    '1/02/2020',    '21633499',  '21636363', '3',   '3'     )
  ,(    '1/02/2020',    '21633499',  '21636363', '1',   '3'     )
  ,(    '1/02/2020',    '21633499',  '21157157', '1',   '2'     )
  ,(    '2/02/2020',    '21633499',  '21157157', '4',   '2'     )
  ,(    '2/02/2020',    '21123456',  '27564564', '4',   '4'     )
  ,(    '2/02/2020',    '21123456',  '27564564', '4',   '4'     )
  ,(    '2/02/2020',    '21123456',  '27564564', '4',   '4'     )
  ,(    '2/02/2020',    '216223344', '27564564', '1',   '1'     )
  ,(    '2/02/2020',    '216223344',  '21157157', '1',  '1'     )
) tbl ([Date], [From_Number], [To_Number], [How_many_calls_to_num_per_day], [How_many_calls_to_num]) 

I tried:

SELECT *, 
    , COUNT ([From_Number]) OVER 
        (PARTITION BY [To_Number], [Date]
    ) AS [How_many_calls_to_num]        
FROM tbl

But I am not getting the desired result and like to know what needs to be done?

In regards to getting the counts per day, would I not just filter the date in the dashboard?

I need to be able to work out who called whom (from - to Numbers), when and how often.

Therefore, I want all these variables in one line for each phone number, like in:

[Date], [From_Number], [To_Number], [How_many_calls]

I have not been able to get the desired result with Grouping sets, also I need to have it in one line as noted above without aggregating the dataset.

Is there a better way to set up the PARTITION BY query?

CodePudding user response：

You can use GROUPING SETS to group at two different groups: to_number, date and to_number only(across dates).

;with cte_tbl as
(
SELECT tbl.*
FROM (
  VALUES

  ( '1/02/2020',    '21123456',  '27564564', '1',   '4'     )
  ,(    '1/02/2020',    '21123456',  '27564564', '1',   '4'     )
  ,(    '1/02/2020',    '21123456',  '21622222', '3',   '1'     )
  ,(    '1/02/2020',    '21633499',  '21636363', '3',   '3'     )
  ,(    '1/02/2020',    '21633499',  '21636363', '3',   '3'     )
  ,(    '1/02/2020',    '21633499',  '21636363', '1',   '3'     )
  ,(    '1/02/2020',    '21633499',  '21157157', '1',   '2'     )
  ,(    '2/02/2020',    '21633499',  '21157157', '4',   '2'     )
  ,(    '2/02/2020',    '21123456',  '27564564', '4',   '4'     )
  ,(    '2/02/2020',    '21123456',  '27564564', '4',   '4'     )
  ,(    '2/02/2020',    '21123456',  '27564564', '4',   '4'     )
  ,(    '2/02/2020',    '216223344', '27564564', '1',   '1'     )
  ,(    '2/02/2020',    '216223344',  '21157157', '1',  '1'     )
) tbl ([Date], [From_Number], [To_Number], [How_many_calls_to_num_per_day], [How_many_calls_to_num]) 
)
SELECT to_number,[date], count(*) as NumberOfCalls FROM cte_tbl
group by 
grouping sets( (to_number,[date]),([to_number]));

In the below table, the row with NULL as date, is across dates for the to_number.

UPDATE As asked in the comments, thanks to @charlieface, you can add from_number as given below.

SELECT from_number, to_number,[date], count(*) as NumberOfCalls FROM cte_tbl
group by 
grouping sets( (from_number,to_number,[date]),(from_number,[to_number]));