asked on

SQL Sub Query

Hi,

I'm trying to build a select statement that returns many queries as one recordset,

by statement actually contains 99 subqueries where a subquery would be one of --

Select
STR(ROUND(AVG(NAF.FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey NAF Where Live = 'Y' AND NAF.FeeCommission > 100

Select
STR(ROUND(AVG(NAR.RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey NAR Where Live = 'Y' AND NAR.RetainerValue > 100

Select
STR(ROUND(AVG(NAT.TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey NAT Where Live = 'Y' AND NAT.TotalRetainer > 100

Open in new window

There are 99,

I then need to return the results of all of the subqueries, into one select statement..

Is there a good way to do this?

Thanks

jogos

You can combine those 3 in one single query

Select
 case when NAF.FeeCommission > 100 then STR(ROUND(AVG(NAF.FeeCommission),0),10,0) else 0 end AS FeeCommission
,case when NAR.RetainerValue > 100 then STR(ROUND(AVG(NAR.RetainerValue),0),10,0) else 0 end  As Retainer
,case when NAT.TotalRetainer > 100 then STR(ROUND(AVG(NAT.TotalRetainer),0),10,0) else 0 end AS Total
From dbo.RetainerSurvey NAF 
Where Live = 'Y'

Open in new window

While you talk about subselects it depends on how you combine all the sub-selects.
You can join that to the result

select a.col1,a.col2 , s1.feecomission, s1.Retainer, s1.Total
from tab1 as a
inner join (select ....
          From dbo.RetainerSurvey NAF  
           Where Live = 'Y') as s1 on (join-condition  ) -- can be dummy 1=1

Open in new window

Also possible

select sum(result1), sum(result2), sum(result3)
(select result1, 0.00 as result2,0.00 as result3 ,0.00 as result3
from tab1
union 
select 0,00 as result1, result2,0.00 as result3 ,0.00 as result3
from tab2
...
)

Open in new window

garethtnash

ASKER

Something like --

Select 
A.FeeCommission,
B.Retainer,
C.Total
From
(Select
STR(ROUND(AVG(NAF.FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey NAF Where Live = 'Y' AND NAF.FeeCommission > 100) A
join (Select
STR(ROUND(AVG(NAR.RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey NAR Where Live = 'Y' AND NAR.RetainerValue > 100) B
join (Select
STR(ROUND(AVG(NAT.TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey NAT Where Live = 'Y' AND NAT.TotalRetainer > 100) C

Open in new window

plummet

I'd go with this:

Select
 	case when FeeCommission > 100 then 
 		STR(ROUND(AVG(FeeCommission),0),10,0) 
 	else 0 
 		end As FeeCommission,
	case when RetainerValue > 100 then 
		STR(ROUND(AVG(RetainerValue),0),10,0) 
	else 0 
		end  As Retainer,
	case when TotalRetainer > 100 then 
		STR(ROUND(AVG(TotalRetainer),0),10,0) 
	else 0 
		end As Total
From dbo.RetainerSurvey 
Where Live = 'Y'

Open in new window

As long as the rest of the 99 queries are also against the same table "RetainerSurvey".

Hope it helps
John

jogos

In my first suggestion I forgot to change the table-aliases (and is same sollution as from Plummet).

The case-solution is just to filter the values apropriate for that column.
Extra join or union is to combine complete different selections when in your other 99 queries you have other tables, other sum's ....

Select sp.Name,sp.Firstname,
s1.FeeCommission,s1.RetainerValue, s1.Total
FROM SalesPerson sp
left join
(select a.SalesPersonID
case when a.FeeCommission > 100 then STR(ROUND(AVG(a.FeeCommission),0),10,0) else 0 end AS FeeCommission
,case when a.RetainerValue > 100 then STR(ROUND(AVG(a.RetainerValue),0),10,0) else 0 end As Retainer
,case when a.TotalRetainer > 100 then STR(ROUND(AVG(a.TotalRetainer),0),10,0) else 0 end AS Total
From dbo.RetainerSurvey a
Where a.Live = 'Y'
) s1 on s1.SalesPersonId = sp.ID

garethtnash

ASKER

Hi Both,

Thanks for your help so far.

Perhaps I should explaina little about what I'm trying to do.

I have a table --

CREATE TABLE [dbo].[RetainerSurvey](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Date] [datetime] NOT NULL,
	[MemberID] [int] NOT NULL,
	[Locality] [int] NOT NULL,
	[RetainerValue] [decimal](10, 2) NOT NULL,
	[FeeCommission] [decimal](10, 2) NOT NULL,
	[TotalRetainer] [decimal](10, 2) NOT NULL,
	[ReviewDate] [datetime] NOT NULL,
	[Comments] [nvarchar](max) NULL,
	[Live] [char](1) NOT NULL
) ON [PRIMARY]

Open in new window

ID is the primary key,
DATE is not needed for this query, nor are MemberID, ReviewDate or Comments.

There are 10 localities 1-10.

So what I'm trying to achieve is;

Average

(Where RetainerValue > 100)
The Average RetainerValue,
The Average RetainerValue for each locality,

(Where FeeCommission > 100)
The Average FeeCommission,
The Average FeeCommission for each locality,

(Where TotalRetainer > 100)
The Average TotalRetainer,
The Average TotalRetainer for each locality,

High

The highest RetainerValue,
The highest RetainerValue for each locality,

The highest FeeCommission,
The highest FeeCommission for each locality,

The highest TotalRetainer,
The highest TotalRetainer for each locality,

Low

(Where RetainerValue > 100)
The Lowest RetainerValue,
The Lowest RetainerValue for each locality,

(Where FeeCommission > 100)
The Lowest FeeCommission,
The Lowest FeeCommission for each locality,

(Where TotalRetainer > 100)
The Lowest TotalRetainer,
The Lowest TotalRetainer for each locality

I'm sure there is an easy way of doing this....

My script is extremely long, and takes over 4 seconds to process --

My code so far is

Select
NatAR.Retainer AverageRetainer,
NatAF.FeeCommission AverageFeeCommission,
NatAT.Total AverageTotal,
NatHR.Retainer HighRetainer,
NatHF.FeeCommission HighFeeCommission,
NatHT.Total HightTotal,
NatLR.Retainer LowRetainer,
NatLF.FeeCommission LowFeeCommission,
NatLT.Total LowTotal,
L1AR.Retainer AvRetainer1,
L1AF.FeeCommission AvFee1,
L1AT.Total AvTotal1,
L2AR.Retainer AvRetainer2,
L2AF.FeeCommission AvFee2,
L2AT.Total AvTotal2,
L3AR.Retainer AvRetainer3,
L3AF.FeeCommission AvFee3,
L3AT.Total AvTotal3,
L4AR.Retainer AvRetainer4,
L4AF.FeeCommission AvFee4,
L4AT.Total AvTotal4,
L5AR.Retainer AvRetainer5,
L5AF.FeeCommission AvFee5,
L5AT.Total AvTotal5,
L6AR.Retainer AvRetainer6,
L6AF.FeeCommission AvFee6,
L6AT.Total AvTotal6,
L7AR.Retainer AvRetainer7,
L7AF.FeeCommission AvFee7,
L7AT.Total AvTotal7,
L8AR.Retainer AvRetainer8,
L8AF.FeeCommission AvFee8,
L8AT.Total AvTotal8,
L9AR.Retainer AvRetainer9,
L9AF.FeeCommission AvFee9,
L9AT.Total AvTotal9,
L10AR.Retainer AvRetainer10,
L10AF.FeeCommission AvFee10,
L10AT.Total AvTotal10
From 
(Select
STR(ROUND(AVG(RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey Where Live = 'Y' AND RetainerValue > 100) NatAR,

(Select
STR(ROUND(AVG(FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey Where Live = 'Y' AND FeeCommission > 100) NatAF,

(Select
STR(ROUND(AVG(TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey Where Live = 'Y' AND TotalRetainer > 100) NatAT,
 
(Select top 1
STR(RetainerValue,10,0) Retainer
From dbo.RetainerSurvey Where Live = 'Y'
order by RetainerValue DESC) NatHR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey Where Live = 'Y'
order by FeeCommission DESC) NatHF,

(Select top 1
STR(TotalRetainer,10,0) Total
From dbo.RetainerSurvey Where Live = 'Y'
order by TotalRetainer DESC) NatHT,

(Select top 1
STR(RetainerValue,10,0) Retainer
From dbo.RetainerSurvey Where Live = 'Y' AND RetainerValue > 100
order by RetainerValue ASC) NatLR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey Where Live = 'Y' AND FeeCommission > 100
order by FeeCommission ASC) NatLF,

(Select top 1
STR(TotalRetainer,10,0) Total
From dbo.RetainerSurvey Where Live = 'Y' AND TotalRetainer > 100
order by TotalRetainer ASC) NatLT,

(Select
STR(ROUND(AVG(RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey
Where Locality = 1 AND Live = 'Y' AND RetainerValue > 100) L1AR,

(Select
STR(ROUND(AVG(FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey
Where Locality = 1 AND Live = 'Y' AND FeeCommission > 100) L1AF,

(Select
STR(ROUND(AVG(TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey
Where Locality = 1 AND Live = 'Y' AND TotalRetainer > 100) L1AT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 1 AND Live = 'Y'
order by RetainerValue DESC) L1HR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 1 AND Live = 'Y'
order by FeeCommission DESC) L1HF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 1 AND Live = 'Y'
order by TotalRetainer DESC) L1HT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 1 AND Live = 'Y' AND RetainerValue > 100
order by RetainerValue ASC) L1LR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 1 AND Live = 'Y' and FeeCommission > 100
order by FeeCommission ASC) L1LF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 1 AND Live = 'Y' AND TotalRetainer > 100
order by TotalRetainer ASC) L1LT,
(Select
STR(ROUND(AVG(RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey
Where Locality = 2 AND Live = 'Y' AND RetainerValue > 100) L2AR,

(Select
STR(ROUND(AVG(FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey
Where Locality = 2 AND Live = 'Y' AND FeeCommission > 100) L2AF,

(Select
STR(ROUND(AVG(TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey
Where Locality = 2 AND Live = 'Y' AND TotalRetainer > 100) L2AT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 2 AND Live = 'Y'
order by RetainerValue DESC) L2HR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 2 AND Live = 'Y'
order by FeeCommission DESC) L2HF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 2 AND Live = 'Y'
order by TotalRetainer DESC) L2HT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 2 AND Live = 'Y' AND RetainerValue > 100
order by RetainerValue ASC) L2LR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 2 AND Live = 'Y' and FeeCommission > 100
order by FeeCommission ASC) L2LF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 2 AND Live = 'Y' AND TotalRetainer > 100
order by TotalRetainer ASC) L2LT,
(Select
STR(ROUND(AVG(RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey
Where Locality = 3 AND Live = 'Y' AND RetainerValue > 100) L3AR,

(Select
STR(ROUND(AVG(FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey
Where Locality = 3 AND Live = 'Y' AND FeeCommission > 100) L3AF,

(Select
STR(ROUND(AVG(TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey
Where Locality = 3 AND Live = 'Y' AND TotalRetainer > 100) L3AT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 3 AND Live = 'Y'
order by RetainerValue DESC) L3HR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 3 AND Live = 'Y'
order by FeeCommission DESC) L3HF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 3 AND Live = 'Y'
order by TotalRetainer DESC) L3HT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 3 AND Live = 'Y' AND RetainerValue > 100
order by RetainerValue ASC) L3LR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 3 AND Live = 'Y' and FeeCommission > 100
order by FeeCommission ASC) L3LF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 3 AND Live = 'Y' AND TotalRetainer > 100
order by TotalRetainer ASC) L3LT,
(Select
STR(ROUND(AVG(RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey
Where Locality = 4 AND Live = 'Y' AND RetainerValue > 100) L4AR,

(Select
STR(ROUND(AVG(FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey
Where Locality = 4 AND Live = 'Y' AND FeeCommission > 100) L4AF,

(Select
STR(ROUND(AVG(TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey
Where Locality = 4 AND Live = 'Y' AND TotalRetainer > 100) L4AT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 4 AND Live = 'Y'
order by RetainerValue DESC) L4HR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 4 AND Live = 'Y'
order by FeeCommission DESC) L4HF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 4 AND Live = 'Y'
order by TotalRetainer DESC) L4HT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 4 AND Live = 'Y' AND RetainerValue > 100
order by RetainerValue ASC) L4LR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 4 AND Live = 'Y' and FeeCommission > 100
order by FeeCommission ASC) L4LF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 4 AND Live = 'Y' AND TotalRetainer > 100
order by TotalRetainer ASC) L4LT,

(Select
STR(ROUND(AVG(RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey
Where Locality = 5 AND Live = 'Y' AND RetainerValue > 100) L5AR,

(Select
STR(ROUND(AVG(FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey
Where Locality = 5 AND Live = 'Y' AND FeeCommission > 100) L5AF,

(Select
STR(ROUND(AVG(TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey
Where Locality = 5 AND Live = 'Y' AND TotalRetainer > 100) L5AT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 5 AND Live = 'Y'
order by RetainerValue DESC) L5HR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 5 AND Live = 'Y'
order by FeeCommission DESC) L5HF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 5 AND Live = 'Y'
order by TotalRetainer DESC) L5HT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 5 AND Live = 'Y' AND RetainerValue > 100
order by RetainerValue ASC) L5LR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 5 AND Live = 'Y' and FeeCommission > 100
order by FeeCommission ASC) L5LF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 5 AND Live = 'Y' AND TotalRetainer > 100
order by TotalRetainer ASC) L5LT,

(Select
STR(ROUND(AVG(RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey
Where Locality = 6 AND Live = 'Y' AND RetainerValue > 100) L6AR,

(Select
STR(ROUND(AVG(FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey
Where Locality = 6 AND Live = 'Y' AND FeeCommission > 100) L6AF,

(Select
STR(ROUND(AVG(TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey
Where Locality = 6 AND Live = 'Y' AND TotalRetainer > 100) L6AT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 6 AND Live = 'Y'
order by RetainerValue DESC) L6HR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 6 AND Live = 'Y'
order by FeeCommission DESC) L6HF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 6 AND Live = 'Y'
order by TotalRetainer DESC) L6HT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 6 AND Live = 'Y' AND RetainerValue > 100
order by RetainerValue ASC) L6LR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 6 AND Live = 'Y' and FeeCommission > 100
order by FeeCommission ASC) L6LF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 6 AND Live = 'Y' AND TotalRetainer > 100
order by TotalRetainer ASC) L6LT,

(Select
STR(ROUND(AVG(RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey
Where Locality = 7 AND Live = 'Y' AND RetainerValue > 100) L7AR,

(Select
STR(ROUND(AVG(FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey
Where Locality = 7 AND Live = 'Y' AND FeeCommission > 100) L7AF,

(Select
STR(ROUND(AVG(TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey
Where Locality = 7 AND Live = 'Y' AND TotalRetainer > 100) L7AT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 7 AND Live = 'Y'
order by RetainerValue DESC) L7HR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 7 AND Live = 'Y'
order by FeeCommission DESC) L7HF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 7 AND Live = 'Y'
order by TotalRetainer DESC) L7HT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 7 AND Live = 'Y' AND RetainerValue > 100
order by RetainerValue ASC) L7LR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 7 AND Live = 'Y' and FeeCommission > 100
order by FeeCommission ASC) L7LF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 7 AND Live = 'Y' AND TotalRetainer > 100
order by TotalRetainer ASC) L7LT,

(Select
STR(ROUND(AVG(RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey
Where Locality = 8 AND Live = 'Y' AND RetainerValue > 100) L8AR,

(Select
STR(ROUND(AVG(FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey
Where Locality = 8 AND Live = 'Y' AND FeeCommission > 100) L8AF,

(Select
STR(ROUND(AVG(TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey
Where Locality = 8 AND Live = 'Y' AND TotalRetainer > 100) L8AT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 8 AND Live = 'Y'
order by RetainerValue DESC) L8HR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 8 AND Live = 'Y'
order by FeeCommission DESC) L8HF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 8 AND Live = 'Y'
order by TotalRetainer DESC) L8HT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 8 AND Live = 'Y' AND RetainerValue > 100
order by RetainerValue ASC) L8LR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 8 AND Live = 'Y' and FeeCommission > 100
order by FeeCommission ASC) L8LF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 8 AND Live = 'Y' AND TotalRetainer > 100
order by TotalRetainer ASC) L8LT,

(Select
STR(ROUND(AVG(RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey
Where Locality = 9 AND Live = 'Y' AND RetainerValue > 100) L9AR,

(Select
STR(ROUND(AVG(FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey
Where Locality = 9 AND Live = 'Y' AND FeeCommission > 100) L9AF,

(Select
STR(ROUND(AVG(TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey
Where Locality = 9 AND Live = 'Y' AND TotalRetainer > 100) L9AT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 9 AND Live = 'Y'
order by RetainerValue DESC) L9HR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 9 AND Live = 'Y'
order by FeeCommission DESC) L9HF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 9 AND Live = 'Y'
order by TotalRetainer DESC) L9HT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 9 AND Live = 'Y' AND RetainerValue > 100
order by RetainerValue ASC) L9LR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 9 AND Live = 'Y' and FeeCommission > 100
order by FeeCommission ASC) L9LF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 9 AND Live = 'Y' AND TotalRetainer > 100
order by TotalRetainer ASC) L9LT,

(Select
STR(ROUND(AVG(RetainerValue),0),10,0) As Retainer
From dbo.RetainerSurvey
Where Locality = 10 AND Live = 'Y' AND RetainerValue > 100) L10AR,

(Select
STR(ROUND(AVG(FeeCommission),0),10,0) AS FeeCommission
From dbo.RetainerSurvey
Where Locality = 10 AND Live = 'Y' AND FeeCommission > 100) L10AF,

(Select
STR(ROUND(AVG(TotalRetainer),0),10,0) AS Total
From dbo.RetainerSurvey
Where Locality = 10 AND Live = 'Y' AND TotalRetainer > 100) L10AT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 10 AND Live = 'Y'
order by RetainerValue DESC) L10HR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 10 AND Live = 'Y'
order by FeeCommission DESC) L10HF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 10 AND Live = 'Y'
order by TotalRetainer DESC) L10HT,

(Select top 1
STR(RetainerValue,10,0) RetainerValue
From dbo.RetainerSurvey
Where Locality = 10 AND Live = 'Y' AND RetainerValue > 100
order by RetainerValue ASC) L10LR,

(Select top 1
STR(FeeCommission,10,0) FeeCommission
From dbo.RetainerSurvey
Where Locality = 10 AND Live = 'Y' and FeeCommission > 100
order by FeeCommission ASC) L10LF,

(Select top 1
STR(TotalRetainer,10,0) TotalRetainer
From dbo.RetainerSurvey
Where Locality = 10 AND Live = 'Y' AND TotalRetainer > 100
order by TotalRetainer ASC) L10LT

Open in new window

Hope my query makes sense and you can point me down a route that loads faster and is tidier?

Thanks

GT

plummet

How about this?

Select
	locality,
 	case when FeeCommission > 100 then 
 		STR(ROUND(AVG(FeeCommission),0),10,0) 
 	else 0 
 		end As AV_FeeCommission,
	case when RetainerValue > 100 then 
		STR(ROUND(AVG(RetainerValue),0),10,0) 
	else 0 
		end  As AV_Retainer,
	case when NAT.TotalRetainer > 100 then 
		STR(ROUND(AVG(TotalRetainer),0),10,0) 
	else 0 
		end As AV_TotalRetainer,
 	case when FeeCommission > 100 then 
 		STR(ROUND(MIN(FeeCommission),0),10,0) 
 	else 0 
 		end As MIN_FeeCommission,
	case when RetainerValue > 100 then 
		STR(ROUND(MIN(RetainerValue),0),10,0) 
	else 0 
		end  As MIN_Retainer,
	case when NAT.TotalRetainer > 100 then 
		STR(ROUND(MIN(TotalRetainer),0),10,0) 
	else 0 
		end As MIN_TotalRetainer,
 	STR(ROUND(MAX(FeeCommission),0),10,0) As MAX_FeeCommission,
	STR(ROUND(MAX(RetainerValue),0),10,0) As MAX_Retainer,
	STR(ROUND(MAX(TotalRetainer),0),10,0) As MAX_TotalRetainer
		
From dbo.RetainerSurvey 
Where Live = 'Y' 
group by Locality

Open in new window

Cheers,
John

garethtnash

ASKER

Not quite --

Msg 8120, Level 16, State 1, Line 3
Column 'dbo.RetainerSurvey.FeeCommission' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "NAT.TotalRetainer" could not be bound.
Msg 4104, Level 16, State 1, Line 23
The multi-part identifier "NAT.TotalRetainer" could not be bound.

:(

ASKER CERTIFIED SOLUTION

plummet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jogos

Introduce the rollup in your group by for also having the overall total

http://msdn.microsoft.com/en-us/library/bb522495.aspx

group by rollup (Lacality)

garethtnash

ASKER

Excellent - thank you John