SQLTeam.com | Weblogs | Forums

Sum with join and where like subquery

Hi

I've been trying to make a query for a while, but I can't quite figure it out. Hopefully someone from this forum can help me :slight_smile:

The result of the query must be the sum from the different types (ivr, term, queue) from table 1 per company name (level 10) from table 1.

What I now have is a subquery in the where part. When I hard put in an ID from table 1 it works. However, with the subquery - which returns multiple lines - it doesn't work.

Can someone help me further with this?

table 1 t1
Name, ID level
company-A 1111 10
company-B 2222 10
user-A 1111 5
user-B 2222 5
user-C 3333 5

table 2 t2
extended_number, type,
1111000 queue
1111
001 term
1111002 term
2222
000 ivr
2222001 term
2222
002 term
2222*003 term

result
t1.Name t1.id t2.count term, t2.count ivr, t.2count queue
company-A 1111 2 0 1
company-B 2222 3 1 0

SELECT
t1.name,
t1.id,
SUM(type LIKE '%ivr%') AS ivr,
SUM(type LIKE '%queue%') AS queue,
SUM(type LIKE '%term%') AS term
FROM t2
where extended_number LIKE ('%', (SELECT id from t1 where level = 10), '%');

Thanks in advance!

Best regards,
Patrick

See image for the contents of the tables and the desired result

Create table #t1 (Name varchar(20), ID int, level int)
insert into #t1 values
('company-A',1111,10),
('company-B',2222,10),
('user-A',1111,5),
('user-B',2222,5),
('user-C',3333,5)

Create table #t2 (extended_number int, type varchar(20))
insert into #t2 values
('1111000','queue'),
('1111001','term'),
('1111002','term'),
('2222000','ivr'),
('2222001','term'),
('2222002','term'),
('2222003','term')


SELECT 
t1.name,
t1.id,
SUM(case when type LIKE '%ivr%' then 1 else 0 end) AS ivr,
SUM(case when type LIKE '%queue%' then 1 else 0 end) AS queue,
SUM(case when type LIKE '%term%' then 1 else 0 end) AS term
FROM #t2 t2
	join #t1 t1
		on extended_number LIKE ('%' + cast(id as varchar(20)) + '%') 
		and level = 10
group by t1.name, t1.id

Dear Mike,

Thanks for your help, it is appreciated!

I have set up both tables and run the query but I get an error back. See below

SELECT
t1.name,
t1.id,
SUM(case when type LIKE '%ivr%' then 1 else 0 end) AS ivr,
SUM(case when type LIKE '%queue%' then 1 else 0 end) AS queue,
SUM(case when type LIKE '%term%' then 1 else 0 end) AS term
FROM t2
join t1
on extended_number LIKE('%' + cast(id as varchar(20)) + '%')
and level = 10
group by t1.name, t1.id LIMIT 0.25

MySQL reports: Documentation
#1064 - There is something wrong in the syntax used with 'varchar(20)) + '%')
and level = 10
group by t1.name, t1.id LIMIT 0.25' in line 9

The expected result should be. Where id from table 1 like extended id from table 2.

t1.Name t1.ID t2.countIVR t2.countterm t2.countqueue
company-A 1111 0 2 1
company-B 2222 1 3 0

This is a microsoft sql server forum. So you will have to find the corresponding syntax for mysql

clear, thanks for the insight with which we finally managed to get the query working in phpmyadmin/mysql

Thanks for sharing more info!