Create a new columns that shows the count based on 2 other columns

Question

I have the following table called call_logs sqlfidle: http://www.sqlfiddle.com/#!18/57b3a/1

Date call_time call_duration
2001-10-01T00:00:00Z 60 30
2001-10-01T00:00:00Z 60 15
2001-10-01T00:00:00Z 180 30
2001-10-05T00:00:00Z. 30 45
2001-02-03T00:00:00Z 90 30
2001-05-03T00:00:00Z 15 15
2001-07-03T00:00:00Z 30 15
2011-10-01T00:00:00Z 60 30
2010-10-01T00:00:00Z 60 30
2007-02-03T00:00:00Z 90 30
I would love to have a new table that show the following result. I want to know how many call_duration of x had a call time of 60, 90,15,120

Date call_time call_duration count_duration
2001-10-01T00:00:00Z 60 30 25457
2001-10-01T00:00:00Z 60 15 458
2001-10-01T00:00:00Z 180 30 2
2001-10-05T00:00:00Z 30 45 788
2001-02-03T00:00:00Z 90 30 489
2001-05-03T00:00:00Z 15 15 1
2001-07-03T00:00:00Z 30 15 689758

I try using this query, but I'm afraid i'll have to enter every single permutation, which I' m not sure it's now the best option
select MAX(date),
SUM( case when call_time = '60' and call_duration = '15' then 1 else 0) as 60_15
,SUM( case when call_time = '60' and call_duration = '30' then 1 else 0) as 60_30
FROM Table1
group by date

Thanks for your help

Answer 1

Hi @learning_step_by_step ,

Please refer below and check whether it is helpful to you.

drop table if exists Table1  
  
CREATE TABLE Table1  
	 ( Date datetime null, call_time int, call_duration int);  
;  
	  
INSERT INTO Table1  
VALUES  
	('2001-10-01T00:00:00Z','60','30'),  
	('2001-10-01T00:00:00Z','60','15'),  
	('2001-10-01T00:00:00Z','180','30'),  
	('2001-10-01T00:00:00Z','30','45'),  
	('2001-02-03T00:00:00Z','90','30'),  
    ('2001-05-03T00:00:00Z','15','15'),  
    ('2001-07-03T00:00:00Z ','30','15'),  
	('2011-10-01T00:00:00Z','60','30'),  
    ('2010-10-01T00:00:00Z','60','30'),  
    ('2007-02-03T00:00:00Z ','90','30')  
  
select MAX(date) date,a.call_time,a.call_duration,cast(a.call_time as varchar(10))+'_'+cast(a.call_duration as varchar(10)) duration,b.Count [Count duration]  
from Table1 a   
inner join (select call_time,call_duration,count(*) count from Table1  
group by call_time,call_duration) b  
on a.call_time=b.call_time and a.call_duration=b.call_duration  
group by a.call_time,a.call_duration,b.count  

Output:

date call_time call_duration duration Count duration  
2001-05-03 00:00:00.000 15 15 15_15 1  
2001-07-03 00:00:00.000 30 15 30_15 1  
2001-10-01 00:00:00.000 30 45 30_45 1  
2001-10-01 00:00:00.000 60 15 60_15 1  
2011-10-01 00:00:00.000 60 30 60_30 3  
2007-02-03 00:00:00.000 90 30 90_30 2  
2001-10-01 00:00:00.000 180 30 180_30 1  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.