select distinct  destination ,sum(answered) as answered,
sum(siezed) as siezed,sum(holdtime) as holdtime,
sum(totalduration) as totalduration,avg(NER) as NER from report_test(nolock)
group by destination order by sum(holdtime) desc

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Report_Destination]
@InAccount varchar(50),
@InTG varchar(50),
@OutAcc varchar(50),
@OutTG varchar(50),
@Country varchar(50),
@Destination varchar(50),
@Calltype nchar(1)='%',
@RouteClass varchar(20)='%',
@Begindate nchar(10),
@EndDate nchar(10),
@Sort varchar(20),
@TOP int
AS
SET ROWCOUNT @TOP
select distinct  destination ,sum(answered) as answered,
sum(siezed) as siezed,sum(holdtime) as holdtime,
sum(totalduration) as totalduration,avg(NER) as NER from report_test(nolock)
where IngressAccount like @InAccount+'%' and IngressTG like @InTG+'%'
and EgressAccount like @OutAcc+'%' and EgressTG like @OutTG+'%' 
and Country like @Country+'%' and Destination like @Destination+'%'
and Date>= @Begindate and Date <=@EndDate group by destination
order by case @Sort
when 'None' then destination
when 'Answered' then sum(answered)
when 'Seized' then   sum(siezed)
when 'HoldTime' then sum(holdtime)
when 'Minutes' then  sum(totalduration)
when 'NER' then  avg(NER)
end

quote:

---

Originally posted by kivancimer

Hello
I want to make a stored procedure but i am having some errors

Query is as below

select destination ,sum(answered) as answered,
sum(siezed) as siezed,sum(holdtime) as holdtime,
sum(totalduration) as totalduration,avg(NER) as NER from report_test(nolock)
group by destination 
order by answered desc

and Stored Procedure is as below

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Report_Destination]
@InAccount varchar(50),
@InTG varchar(50),
@OutAcc varchar(50),
@OutTG varchar(50),
@Country varchar(50),
@Destination varchar(50),
@Calltype nchar(1)='%',
@RouteClass varchar(20)='%',
@Begindate nchar(10),
@EndDate nchar(10),
@Sort varchar(20),
@TOP int
AS
SET ROWCOUNT @TOP
select distinct  destination ,sum(answered) as answered,
sum(siezed) as siezed,sum(holdtime) as holdtime,
sum(totalduration) as totalduration,avg(NER) as NER from report_test(nolock)
where IngressAccount like @InAccount+'%' and IngressTG like @InTG+'%'
and EgressAccount like @OutAcc+'%' and EgressTG like @OutTG+'%' 
and Country like @Country+'%' and Destination like @Destination+'%'
and Date>= @Begindate and Date <=@EndDate group by destination
order by case @Sort
when 'None' then destination
when 'Answered' then sum(answered)
when 'Seized' then   sum(siezed)
when 'HoldTime' then sum(holdtime)
when 'Minutes' then  sum(totalduration)
when 'NER' then  avg(NER)
end

I am having this error
Msg 145, Level 15, State 1, Procedure Report_Destination, Line 16
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I would be grateful if anybody can help..
thanks in advance
Kivanc

---