November 12, 2013 at 11:52 pm
I have the query below, I'd like to display the sum([Inbound Calls]) and Sum([Accepted Calls]) both grouped by [MonthID], along with [MonthID] and [Hangups] which already displays in the query.
I can't figure out what I'm missing, if anybody can give me a tip I'd really appreciate it.
select a.[MonthID],k.[Hangups] as "Hangups"
from dbo.VisaNACallSumMonthID as a left outer join (select m.[MonthID], sum(m.[Inbound Calls])-(Select sum(c.[Accepted Calls])
from dbo.VisaNA_ProdMonthID as c where c.[QVDN] = '2014649' and c.[MonthID] = m.[MonthID]) as "Hangups"
from dbo.VisaNACallSumMonthID as m where m.[VDN] = 2017686 group by m.[MonthID]) as k on a.[MonthID] = k.[MonthID]
November 13, 2013 at 12:21 am
Your subqueries are wrong. For example, (Select sum(c.[Accepted Calls]) doesn't have a FROM clause.
It might help if you provided table DDL, sample data and desired output.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 13, 2013 at 10:20 am
Thanks for getting back to me so quickly on this. The subqueries are correlated subqueries, and the Select Sum([Accepted Calls]) has a from clause. I've underlined it below. I'm sorry for the sloppy post, I'm new to SQL Server Central.
Right now when I run this query it gives me the MonthID and the subtraction for hangups grouped by the MonthID. I'd like to get the Sum([Accepted Calls]) and Sum([Inbound Calls]) also grouped by MonthID. It seems like that should be simple, but I'm missing something.
select a.[MonthID],k.[Hangups] as "Hangups"
from dbo.VisaNACallSumMonthID as a left outer join (select m.[MonthID], sum(m.[Inbound Calls])-(Select sum(c.[Accepted Calls])
from dbo.VisaNA_ProdMonthID as c where c.[QVDN] = '2014649' and c.[MonthID] = m.[MonthID]) as "Hangups"
from dbo.VisaNACallSumMonthID as m where m.[VDN] = 2017686 group by m.[MonthID]) as k on a.[MonthID] = k.[MonthID]
November 13, 2013 at 12:16 pm
I saw this thread earlier and totally agreed with Koen about the syntax errors. The way it was posted is just completely illegible. I took your post and ran it through a formatter so it is a LOT easier to see what is going on. It seems that the code posted will compile...
SELECT a.[MonthID]
,k.[Hangups] AS "Hangups"
FROM dbo.VisaNACallSumMonthID AS a
LEFT JOIN (
SELECT m.[MonthID]
,sum(m.[Inbound Calls]) - (
SELECT sum(c.[Accepted Calls])
FROM dbo.VisaNA_ProdMonthID AS c
WHERE c.[QVDN] = '2014649'
AND c.[MonthID] = m.[MonthID]
) AS "Hangups"
FROM dbo.VisaNACallSumMonthID AS m
WHERE m.[VDN] = 2017686
GROUP BY m.[MonthID]
) AS k ON a.[MonthID] = k.[MonthID]
Now it seems that you still need some help. It is really difficult to determine what is going on here because we don't have the tables to work with. The best approach for this type of thing is to post ddl, sample data and desired output. Please take a few minutes and read the first article linked in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 13, 2013 at 12:30 pm
scotsditch (11/13/2013)
Thanks for getting back to me so quickly on this. The subqueries are correlated subqueries, and the Select Sum([Accepted Calls]) has a from clause. I've underlined it below. I'm sorry for the sloppy post, I'm new to SQL Server Central.
Right 🙂
Counting brackets isn't my strong suit. 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 16, 2013 at 3:13 pm
Sean, thank you for the tip. Sorry I haven't gotten back to you sooner, been tied up at work. I definitely appreciate you taking the time too look at this. I have the table ddl for the two tables below:
--Table DDL for Table VisaNA_ProdMonthID --
CREATE TABLE [dbo].[VisaNA_ProdMonthID](
[MonthID] [numeric](18, 0) NULL,
[Calendar Month] [varchar](50) NULL,
[Product] [varchar](50) NULL,
[QVDN] [varchar](50) NULL,
[Inbound Calls] [numeric](18, 0) NULL,
[Accepted Calls] [numeric](18, 0) NULL,
[Answered Calls] [varchar](50) NULL,
[Calls < 20 Sec] [varchar](50) NULL,
[% of Calls < 20 Sec] [varchar](50) NULL,
[Avg Speed Answer] [varchar](50) NULL,
[Max Delay] [varchar](50) NULL,
[Avg Talk Time] [varchar](50) NULL,
[Abandoned Calls] [varchar](50) NULL,
[Avg Abandoned Time] [varchar](50) NULL,
[Abandoned > 10 Sec] [varchar](50) NULL,
[% Abandoned > 10 Sec] [varchar](50) NULL
) ON [PRIMARY]
--Table DDL For Table VisaNACallSumMonthID--
CREATE TABLE [dbo].[VisaNACallSumMonthID](
[MonthID] [numeric](18, 0) NULL,
[VDN] [varchar](50) NULL,
[Date] [varchar](50) NULL,
[Time] [varchar](50) NULL,
[Inbound Calls] [numeric](18, 0) NULL,
[Other Calls] [varchar](50) NULL,
[Calls Offered] [numeric](18, 0) NULL,
[ACD Calls] [numeric](18, 0) NULL,
[Calls Ans <15] [varchar](50) NULL,
[Total Answer Time] [varchar](50) NULL,
[Max Delay] [varchar](50) NULL,
[Total Talk Time] [varchar](50) NULL,
[Aban Calls] [varchar](50) NULL,
[Calls Aban >20] [varchar](50) NULL,
[ABNTIME] [varchar](50) NULL
) ON [PRIMARY]
--Insert test data into table VisaNA_ProdMonthID---
INSERT INTO [dbo].[VisaNA_ProdMonthID]
([MonthID],[Calendar Month], [Product], [QVDN],[Inbound Calls],[Inbound Calls],[Answered Calls],[Calls < 20 Sec],[Avg Speed Answer],[Max Delay],[Avg Talk Time],[Abandoned Calls],[Avg Abandoned Time],[Abandoned > 10 Sec],[% Abandoned > 10 Sec])
SELECT'1','Sep-13','','','9004','8985','8771','7822','0:00:18','0:41:08','0:05:40','214','0:01:21','111','1.24%',UNION ALL
SELECT'1','','Infinite English','','1597','1594','1546','1381','0:00:19','0:41:08','0:05:21','48','0:00:31','27','1.69%',UNION ALL
SELECT'1','','','2014587','1597','1594','1546','1381','0:00:19','0:41:08','0:05:21','48','0:00:31','27','1.69%',UNION ALL
SELECT'1','','Infinite English Mobile','','12','12','12','12','0:00:08','0:00:11','0:02:00','0','0:00:00','0','0.00%',UNION ALL
SELECT'1','','','2014603','12','12','12','12','0:00:08','0:00:11','0:02:00','0','0:00:00','0','0.00%',UNION ALL
SELECT'1','','Infinite French','','92','92','86','76','0:00:21','0:05:09','0:06:00','6','0:00:21','2','2.17%',UNION ALL
SELECT'1','','','2014594','92','92','86','76','0:00:21','0:05:09','0:06:00','6','0:00:21','2','2.17%',UNION ALL
SELECT'1','','Signature','','7283','7267','7109','6336','0:00:18','0:37:27','0:05:45','158','0:01:40','82','1.13%',UNION ALL
SELECT'1','','','2014579','7012','6997','6846','6101','0:00:18','0:37:27','0:05:45','151','0:01:42','79','1.13%',UNION ALL
SELECT'1','','','2014580','243','242','236','213','0:00:18','0:07:33','0:05:36','6','0:00:49','2','0.83%',UNION ALL
SELECT'1','','','2014581','16','16','15','14','0:00:17','0:02:29','0:06:48','1','0:01:37','1','6.25%',UNION ALL
SELECT'1','','','2014585','4','4','4','4','0:00:11','0:00:14','0:03:44','0','0:00:00','0','0.00%',UNION ALL
SELECT'1','','','2014586','8','8','8','4','0:00:50','0:02:36','0:11:49','0','0:00:00','0','0.00%',UNION ALL
SELECT'1','','SSA','','6','6','6','6','0:02:12','0:05:57','0:03:38','0','0:00:00','0','0.00%',UNION ALL
SELECT'1','','','2018133','6','6','6','6','0:02:12','0:05:57','0:03:38','0','0:00:00','0','0.00%',UNION ALL
SELECT'1','','TD Bank','','2','2','2','2','0:00:08','0:00:09','0:05:58','0','0:00:00','0','0.00%',UNION ALL
SELECT'1','','','2014649','2','2','2','2','0:00:08','0:00:09','0:05:58','0','0:00:00','0','0.00%',UNION ALL
SELECT'1','','Williams-Sonoma','','12','12','10','9','0:00:15','0:01:25','0:04:54','2','0:00:01','0','0.00%',UNION ALL
SELECT'1','','','2018185','12','12','10','9','0:00:15','0:01:25','0:04:54','2','0:00:01','0','0.00%',UNION ALL
SELECT'2','Oct-13','','','8920','8896','8713','8003','0:00:16','0:18:28','0:05:47','183','0:01:22','82','0.92%',UNION ALL
SELECT'2','','Infinite English','','1458','1456','1428','1312','0:00:15','0:11:10','0:05:34','28','0:01:10','17','1.17%',UNION ALL
SELECT'2','','','2014587','1458','1456','1428','1312','0:00:15','0:11:10','0:05:34','28','0:01:10','17','1.17%',UNION ALL
SELECT'2','','Infinite English Mobile','','6','6','3','3','0:00:07','0:00:08','0:01:14','3','0:00:04','0','0.00%',UNION ALL
SELECT'2','','','2014603','6','6','3','3','0:00:07','0:00:08','0:01:14','3','0:00:04','0','0.00%',UNION ALL
SELECT'2','','Infinite French','','96','95','92','80','0:00:30','0:08:29','0:06:02','3','0:01:08','1','1.05%',UNION ALL
SELECT'2','','','2014594','96','95','92','80','0:00:30','0:08:29','0:06:02','3','0:01:08','1','1.05%',UNION ALL
SELECT'2','','Signature','','7332','7311','7170','6590','0:00:16','0:18:28','0:05:50','141','0:01:24','64','0.88%',UNION ALL
SELECT'2','','','2014579','7014','6995','6861','6301','0:00:16','0:18:28','0:05:49','134','0:01:26','61','0.87%',UNION ALL
SELECT'2','','','2014580','285','284','278','261','0:00:14','0:05:36','0:05:53','6','0:01:02','3','1.06%',UNION ALL
SELECT'2','','','2014581','22','21','20','19','0:00:13','0:01:34','0:08:47','1','0:00:02','0','0.00%',UNION ALL
SELECT'2','','','2014585','2','2','2','2','0:00:08','0:00:10','0:05:13','0','0:00:00','0','0.00%',UNION ALL
SELECT'2','','','2014586','9','9','9','7','0:00:14','0:00:39','0:10:37','0','0:00:00','0','0.00%',UNION ALL
SELECT'2','','SSA','','20','20','12','12','0:00:42','0:07:46','0:09:00','8','0:01:55','0','0.00%',UNION ALL
SELECT'2','','','2018133','14','14','10','10','0:00:48','0:04:19','0:10:00','4','0:01:06','0','0.00%',UNION ALL
SELECT'2','','','2018134','6','6','2','2','0:00:12','0:07:46','0:03:56','4','0:02:44','0','0.00%',UNION ALL
SELECT'2','','TD Bank','','3','3','3','3','0:00:09','0:00:12','0:07:27','0','0:00:00','0','0.00%',UNION ALL
SELECT'2','','','2014649','3','3','3','3','0:00:09','0:00:12','0:07:27','0','0:00:00','0','0.00%',UNION ALL
SELECT'2','','Williams-Sonoma','','5','5','5','3','0:01:01','0:03:51','0:03:41','0','0:00:00','0','0.00%',UNION ALL
SELECT'2','','','2018185','5','5','5','3','0:01:01','0:03:51','0:03:41','0','0:00:00','0','0.00%',
----Insert test data into table VisaNACallSumMonthID---
INSERT INTO [dbo].[VisaNACallSumMonthID]
([MonthID],[VDN],[Date],[Time],[Inbound Calls],[Other Calls],[Calls Offered],[ACD Calls],[Calls Ans <15],[Total Answer Time],[Max Delay],[Total Talk Time],[Aban Calls],[Calls Aban >20],[ABNTIME])
SELECT'1','2017686','9/1/2013','19:30','1','1','0','0','0','0','17','0','0','0','0',UNION ALL
SELECT'1','2017686','9/3/2013','8:30','1','0','1','0','0','0','2','0','1','0','2',UNION ALL
SELECT'1','2017686','9/3/2013','15:00','1','0','1','0','0','0','5','0','1','0','5',UNION ALL
SELECT'1','2017686','9/4/2013','9:00','1','1','0','0','0','0','18','0','0','0','0',UNION ALL
SELECT'1','2017686','9/5/2013','8:30','1','1','0','0','0','0','14','0','0','0','0',UNION ALL
SELECT'1','2017686','9/5/2013','16:00','1','0','1','0','0','0','4','0','1','0','4',UNION ALL
SELECT'1','2017686','9/10/2013','5:30','1','0','1','0','0','0','26','0','1','0','26',UNION ALL
SELECT'1','2017686','9/10/2013','9:00','1','1','0','0','0','0','21','0','0','0','0',UNION ALL
SELECT'1','2017686','9/10/2013','13:30','1','1','0','0','0','0','29','0','0','0','0',UNION ALL
SELECT'1','2017686','9/12/2013','20:00','1','1','0','0','0','0','51','0','0','0','0',UNION ALL
SELECT'1','2017686','9/13/2013','12:00','1','1','0','0','0','0','23','0','0','0','0',UNION ALL
SELECT'1','2017686','9/14/2013','13:30','3','2','1','0','0','0','21','0','1','0','8',UNION ALL
SELECT'1','2017686','9/15/2013','17:30','1','0','1','0','0','0','65','0','1','0','65',UNION ALL
SELECT'1','2017686','9/16/2013','7:00','0','0','0','0','0','0','0','0','0','0','0',UNION ALL
SELECT'1','2017686','9/16/2013','7:30','1','1','0','0','0','0','46','0','0','0','0',UNION ALL
SELECT'1','2017686','9/16/2013','10:00','1','1','0','0','0','0','14','0','0','0','0',UNION ALL
SELECT'1','2017686','9/16/2013','11:00','1','1','0','0','0','0','15','0','0','0','0',UNION ALL
SELECT'1','2017686','9/16/2013','13:30','0','0','0','0','0','0','0','0','0','0','0',UNION ALL
SELECT'1','2017686','9/16/2013','14:00','1','1','0','0','0','0','22','0','0','0','0',UNION ALL
SELECT'1','2017686','9/17/2013','13:00','0','0','0','0','0','0','0','0','0','0','0',UNION ALL
SELECT'1','2017686','9/17/2013','13:30','1','1','0','0','0','0','22','0','0','0','0',UNION ALL
SELECT'1','2017686','9/25/2013','10:30','1','1','0','0','0','0','29','0','0','0','0',UNION ALL
SELECT'1','2017686','9/26/2013','5:00','1','1','0','0','0','0','43','0','0','0','0',UNION ALL
SELECT'1','2017686','9/27/2013','8:30','1','1','0','0','0','0','46','0','0','0','0',UNION ALL
SELECT'1','2017686','9/27/2013','14:00','0','0','0','0','0','0','0','0','0','0','0',UNION ALL
SELECT'1','2017686','9/27/2013','14:30','1','1','0','0','0','0','32','0','0','0','0',UNION ALL
SELECT'1','2017686','9/27/2013','22:00','1','0','1','0','0','0','44','0','1','0','44',UNION ALL
SELECT'1','2017686','9/29/2013','13:30','1','0','1','0','0','0','6','0','1','0','6',UNION ALL
SELECT'1','2017686','9/30/2013','11:00','1','1','0','0','0','0','21','0','0','0','0',UNION ALL
SELECT'2','2017686','10/3/2013','23:00','1','0','1','0','0','0','30','0','1','0','30',UNION ALL
SELECT'2','2017686','10/3/2013','23:30','1','1','0','0','0','0','22','0','0','0','0',UNION ALL
SELECT'2','2017686','10/4/2013','14:00','0','0','0','0','0','0','0','0','0','0','0',UNION ALL
SELECT'2','2017686','10/4/2013','14:30','1','1','0','0','0','0','21','0','0','0','0',UNION ALL
SELECT'2','2017686','10/5/2013','19:00','1','1','0','0','0','0','111','0','0','0','0',UNION ALL
SELECT'2','2017686','10/6/2013','17:00','1','0','1','0','0','0','36','0','1','0','36',UNION ALL
SELECT'2','2017686','10/7/2013','13:30','1','0','1','0','0','0','7','0','1','0','7',UNION ALL
SELECT'2','2017686','10/8/2013','11:30','2','0','2','0','0','0','8','0','2','0','15',UNION ALL
SELECT'2','2017686','10/9/2013','7:00','1','1','0','0','0','0','14','0','0','0','0',UNION ALL
SELECT'2','2017686','10/9/2013','7:30','1','1','0','0','0','0','11','0','0','0','0',UNION ALL
SELECT'2','2017686','10/9/2013','8:00','0','0','0','0','0','0','0','0','0','0','0',UNION ALL
SELECT'2','2017686','10/9/2013','8:30','1','1','0','0','0','0','42','0','0','0','0',UNION ALL
SELECT'2','2017686','10/9/2013','14:30','3','3','0','0','0','0','52','0','0','0','0',UNION ALL
SELECT'2','2017686','10/9/2013','17:30','1','1','0','0','0','0','49','0','0','0','0',UNION ALL
SELECT'2','2017686','10/11/2013','15:30','1','1','0','0','0','0','17','0','0','0','0',UNION ALL
SELECT'2','2017686','10/12/2013','8:00','1','1','0','0','0','0','30','0','0','0','0',UNION ALL
SELECT'2','2017686','10/14/2013','13:00','1','1','0','0','0','0','22','0','0','0','0',UNION ALL
SELECT'2','2017686','10/15/2013','14:30','1','1','0','0','0','0','44','0','0','0','0',UNION ALL
SELECT'2','2017686','10/18/2013','14:30','1','1','0','0','0','0','22','0','0','0','0',UNION ALL
SELECT'2','2017686','10/20/2013','12:00','0','0','0','0','0','0','0','0','0','0','0',UNION ALL
SELECT'2','2017686','10/20/2013','12:30','1','1','0','0','0','0','22','0','0','0','0',UNION ALL
SELECT'2','2017686','10/20/2013','16:00','1','1','0','0','0','0','23','0','0','0','0',UNION ALL
SELECT'2','2017686','10/20/2013','17:30','1','1','0','0','0','0','15','0','0','0','0',UNION ALL
SELECT'2','2017686','10/21/2013','10:30','0','0','0','0','0','0','0','0','0','0','0',UNION ALL
SELECT'2','2017686','10/21/2013','11:00','1','1','0','0','0','0','30','0','0','0','0',UNION ALL
SELECT'2','2017686','10/22/2013','18:30','1','0','1','0','0','0','40','0','1','0','40',UNION ALL
SELECT'2','2017686','10/23/2013','15:00','1','0','1','0','0','0','1','0','1','0','1',UNION ALL
SELECT'2','2017686','10/25/2013','5:30','1','1','0','0','0','0','31','0','0','0','0',UNION ALL
SELECT'2','2017686','10/25/2013','15:30','1','1','0','0','0','0','26','0','0','0','0',UNION ALL
SELECT'2','2017686','10/25/2013','18:30','1','0','1','0','0','0','53','0','1','0','53',UNION ALL
SELECT'2','2017686','10/26/2013','9:00','1','0','1','0','0','0','29','0','1','0','29',UNION ALL
SELECT'2','2017686','10/27/2013','13:00','1','1','0','0','0','0','30','0','0','0','0',UNION ALL
SELECT'2','2017686','10/28/2013','10:00','1','1','0','0','0','0','16','0','0','0','0',UNION ALL
SELECT'2','2017686','10/28/2013','14:30','1','1','0','0','0','0','43','0','0','0','0',UNION ALL
SELECT'2','2017686','10/29/2013','12:30','1','0','1','0','0','0','30','0','1','0','30',UNION ALL
SELECT'2','2017686','10/30/2013','6:30','1','0','1','0','0','0','24','0','1','0','24',UNION ALL
SELECT'2','2017686','10/30/2013','13:30','1','1','0','0','0','0','30','0','0','0','0',UNION ALL
SELECT'2','2017686','10/30/2013','14:00','1','1','0','0','0','0','49','0','0','0','0',UNION ALL
SELECT'2','2017686','10/31/2013','9:30','1','1','0','0','0','0','22','0','0','0','0',UNION ALL
SELECT'2','2017686','10/31/2013','16:00','1','0','1','0','0','0','24','0','1','0','24',
Please let me know if you have any other advice on how I can improve my posts.
November 16, 2013 at 5:07 pm
scotsditch (11/16/2013)
Please let me know if you have any other advice on how I can improve my posts.
I'd strongly recommend you try to run your code before you post it. Run the code you just posted and see what I'm talking about. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2013 at 8:58 am
Jeff Moden (11/16/2013)
scotsditch (11/16/2013)
Please let me know if you have any other advice on how I can improve my posts.I'd strongly recommend you try to run your code before you post it. Run the code you just posted and see what I'm talking about. 😉
Yes, quite so. Although the hundred and odd syntax errors look easily fixable, they will definitely put people off.
Tom
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply