April 19, 2012 at 8:49 am
Hi I hope someone can help
I am relativley new to using sql so i apoligise before hand if this is a simple question
I have the following 2 select statements and im looking to combine them into one query
Select BchCode,
SUM(localOffd) As 'Local Offd Calls',
Sum(LocalAns) As 'Local Ans',
SUM(LocalTrip) As 'Local Trip',
SUM(LocalLess60Secs) As 'Local Calls < 60 Seconds',
Sum(LocalGreat5Mins) As 'Local Calls > 5 Min',
SUM(MarketingOffd) As 'Marketing Offd Calls',
Sum(MarketingAns) As 'Marketing Ans',
SUM(MarketingTrip) As 'Marketing Trip',
SUM(MarketingLess60Secs) As 'Marketing Calls < 60 Seconds',
Sum(MarketingGreat5Mins) As 'Marketing Calls > 5 Min',
SUM(BoughtOffd) As 'Bought Offd Calls',
Sum(BoughtAns) As 'Bought Ans',
SUM(BoughtTrip) As 'Bought Trip',
SUM(BoughtLess60Secs) As 'Bought Calls < 60 Seconds',
Sum(BoughtGreat5Mins) As 'Bought Calls > 5 Min'
From DailyBchDataInitialInbound
Where BchHrsFlag = 1
Group By bchCode
Order By BchCode
April 19, 2012 at 8:51 am
Sorry i posted the question before i completed it
1st Select Statement
Select BchCode,
SUM(localOffd) As 'Local Offd Calls',
Sum(LocalAns) As 'Local Ans',
SUM(LocalTrip) As 'Local Trip',
SUM(LocalLess60Secs) As 'Local Calls < 60 Seconds',
Sum(LocalGreat5Mins) As 'Local Calls > 5 Min',
SUM(MarketingOffd) As 'Marketing Offd Calls',
Sum(MarketingAns) As 'Marketing Ans',
SUM(MarketingTrip) As 'Marketing Trip',
SUM(MarketingLess60Secs) As 'Marketing Calls < 60 Seconds',
Sum(MarketingGreat5Mins) As 'Marketing Calls > 5 Min',
SUM(BoughtOffd) As 'Bought Offd Calls',
Sum(BoughtAns) As 'Bought Ans',
SUM(BoughtTrip) As 'Bought Trip',
SUM(BoughtLess60Secs) As 'Bought Calls < 60 Seconds',
Sum(BoughtGreat5Mins) As 'Bought Calls > 5 Min'
From DailyBchDataInitialInbound
Where BchHrsFlag = 1
Group By bchCode
Order By BchCode
2nd Select Statement
Select ClusterOffdBchCode As 'Bch Code',
Count(ClusterOffdBchCode) As 'Cluster Offd',
Sum(ClusterTrip) As 'Cluster Trip',
SUM(ClusterAns) As 'Cluster Ans',
SUM(ClusterLess60Secs) As 'Cluster Calls < 60',
Sum(ClusterGreat5Min) As 'Cluster > 5 Min'
from DailyBchDataInitialInbound
Where BchHrsFlag = 1
Group By ClusterOffdBchCode
April 19, 2012 at 9:00 am
mark.doyle (4/19/2012)
Sorry i posted the question before i completed it1st Select Statement
Select BchCode,
SUM(localOffd) As 'Local Offd Calls',
Sum(LocalAns) As 'Local Ans',
SUM(LocalTrip) As 'Local Trip',
SUM(LocalLess60Secs) As 'Local Calls < 60 Seconds',
Sum(LocalGreat5Mins) As 'Local Calls > 5 Min',
SUM(MarketingOffd) As 'Marketing Offd Calls',
Sum(MarketingAns) As 'Marketing Ans',
SUM(MarketingTrip) As 'Marketing Trip',
SUM(MarketingLess60Secs) As 'Marketing Calls < 60 Seconds',
Sum(MarketingGreat5Mins) As 'Marketing Calls > 5 Min',
SUM(BoughtOffd) As 'Bought Offd Calls',
Sum(BoughtAns) As 'Bought Ans',
SUM(BoughtTrip) As 'Bought Trip',
SUM(BoughtLess60Secs) As 'Bought Calls < 60 Seconds',
Sum(BoughtGreat5Mins) As 'Bought Calls > 5 Min'
From DailyBchDataInitialInbound
Where BchHrsFlag = 1
Group By bchCode
Order By BchCode
2nd Select Statement
Select ClusterOffdBchCode As 'Bch Code',
Count(ClusterOffdBchCode) As 'Cluster Offd',
Sum(ClusterTrip) As 'Cluster Trip',
SUM(ClusterAns) As 'Cluster Ans',
SUM(ClusterLess60Secs) As 'Cluster Calls < 60',
Sum(ClusterGreat5Min) As 'Cluster > 5 Min'
from DailyBchDataInitialInbound
Where BchHrsFlag = 1
Group By ClusterOffdBchCode
Please refer to the link in my signature by Jeff Moden to give us the information we need to help you. I can first tell you that you have a different number of columns, which makes me think I don't understand your question... Also, don't use spaces or special characters in your column names. Save that for after the SQL is complete and you can render the column names in your report or whatever.
Jared
CE - Microsoft
April 19, 2012 at 9:09 am
Hi Jared
Thanks for the reply i have moved my spaces and characters from my column names, sorry about the way i posted the question i will follow the guidelines next time
I know that there are not the same colums in both select statements what i am trying to do is add the 5 columns in the second select statement to the end of the colums on the First statement
Hopes this makes sense
Thanls again
April 19, 2012 at 9:10 am
Try this, it is the best I can do without sample data and expected results:
Code fixed below
Jared
CE - Microsoft
April 19, 2012 at 9:14 am
Hold on... Need to fix some syntax...
Jared
CE - Microsoft
April 19, 2012 at 9:17 am
;WITH cte (BchCode, ClusterOffdBchCode, ClusterTrip,
ClusterAns, ClusterLess60Secs, ClusterGreat5Min) AS
(
SELECT ClusterOffdBchCode AS BchCode,
COUNT(ClusterOffdBchCode) AS ClusterOffdBchCode,
SUM(ClusterTrip) AS ClusterTrip,
SUM(ClusterAns) AS ClusterAns,
SUM(ClusterLess60Secs) AS ClusterLess60Secs,
SUM(ClusterGreat5Min) AS ClusterGreat5Min
FROM DailyBchDataInitialInbound
WHERE BchHrsFlag = 1
GROUP BY ClusterOffdBchCode
)
SELECT BchCode,
SUM(localOffd) AS localOffd,
SUM(LocalAns) AS LocalAns,
SUM(LocalTrip) AS LocalTrip,
SUM(LocalLess60Secs) AS LocalLess60Secs,
SUM(LocalGreat5Mins) AS LocalGreat5Mins,
SUM(MarketingOffd) AS MarketingOffd,
SUM(MarketingAns) AS MarketingAns,
SUM(MarketingTrip) AS MarketingTrip,
SUM(MarketingLess60Secs) AS MarketingLess60Secs,
SUM(MarketingGreat5Mins) AS MarketingGreat5Mins,
SUM(BoughtOffd) AS BoughtOffd,
SUM(BoughtAns) AS BoughtAns,
SUM(BoughtTrip) AS BoughtTrip,
SUM(BoughtLess60Secs) AS BoughtLess60Secs,
SUM(BoughtGreat5Mins) AS BoughtGreat5Mins,
cte.ClusterOffdBchCode,
cte.ClusterTrip,
cte.ClusterAns,
cte.ClusterLess60Secs,
cte.ClusterGreat5Min
FROM DailyBchDataInitialInbound bc1
LEFT JOIN cte
ON bc1.BchCode = cte.BchCode
WHERE bc1.BchHrsFlag = 1
GROUP BY bc1.bchCode,
cte.ClusterOffdBchCode,
cte.ClusterTrip,
cte.ClusterAns,
cte.ClusterLess60Secs,
cte.ClusterGreat5Min
Jared
CE - Microsoft
April 20, 2012 at 1:23 am
Hi Jared
Thank you for your help its much aprreciated
when i run the code i am getting the following error
Column 'cte.ClusterTrip' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
i have tried adding ClusterTrip to the groupby Clause but this does not fix the issue
April 20, 2012 at 5:52 am
Ok, I just added those to the group by clause. Look back at the last post, I changed it there.
Jared
CE - Microsoft
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply