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
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.
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), '%');
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
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
