select
ROW_NUMBER() OVER(order by (((sum(Saleability) + sum(Quality) + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10) / count(*)) desc) as Position,
brandid,
B.Name,
/*sum(Saleability) TotalSaleability,*/
sum(Saleability) /count(*) as avgSaleability,
/*sum(Quality) TotalQuality,*/
sum(Quality) /count(*) as avgQuality,
/*sum(Availability) TotalAvailability,*/
sum(Availability) /count(*) as avgAvailability,
/*sum(Delivery) TotalDelivery,*/
sum(Delivery) /count(*) as avgDelivery,
/*sum([Returns]) TotalReturns,*/
sum(Returns) /count(*) as avgReturns,
/*sum(RetailPrice) TotalRetailPrice,*/
sum(RetailPrice) /count(*) as avgRetailPrice,
/*sum(ServiceSupport) TotalServiceSupport,*/
sum(ServiceSupport) /count(*) as avgServiceSupport,
/*sum(Rep) TotalRep,*/
sum(Rep) /count(*) as avgRep,
/*sum(Accounts) TotalAccounts,*/
sum(Accounts) /count(*) as avgAccounts,
/*sum(CreditControl) TotalCreditControl,*/
sum(CreditControl) /count(*) as avgCreditControl,
(((sum(Saleability) + sum(Quality) + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10) / count(*)) Total,
count(*) Stockists,
sum(case when nextyeartrade = 'Y' then 1 else 0 end) as StockistsNY
from [dbo].[SupplierSurveyInput] S
inner join dbo.Brand B
on S.BrandID = B.ID
where [CYearStockist] = 'Y'
group by b.name, brandid, SupplierSurveyID
order by Total desc
CREATE TABLE [dbo].[SupplierSurveyInput](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SupplierSurveyID] [int] NULL,
[BrandID] [int] NULL,
[PartnerID] [int] NULL,
[CYearStockist] [char](1) NULL,
[CYearNStockistWhy] [int] NULL,
[CountryID] [int] NULL,
[Saleability] [decimal](5, 2) NULL,
[Quality] [decimal](5, 2) NULL,
[Availability] [decimal](5, 2) NULL,
[Delivery] [decimal](5, 2) NULL,
[Returns] [decimal](5, 2) NULL,
[RetailPrice] [decimal](5, 2) NULL,
[ServiceSupport] [decimal](5, 2) NULL,
[Rep] [decimal](5, 2) NULL,
[Accounts] [decimal](5, 2) NULL,
[CreditControl] [decimal](5, 2) NULL,
[NextYearTrade] [char](1) NULL,
[NoTradeWhy] [int] NULL,
[Completed] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SupplierSurveyInput] ADD DEFAULT ('N') FOR [CYearStockist]
GO
ALTER TABLE [dbo].[SupplierSurveyInput] ADD DEFAULT ('N') FOR [NextYearTrade]
GO
ALTER TABLE [dbo].[SupplierSurveyInput] ADD DEFAULT ('N') FOR [Completed]
GO
CREATE TABLE [dbo].[Brand](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SupplierID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Add1] [nvarchar](255) NULL,
[Add2] [nvarchar](255) NULL,
[City] [int] NULL,
[County] [int] NULL,
[Country] [int] NULL,
[Postcode] [nvarchar](10) NULL,
[SwitchBoard] [nvarchar](20) NULL,
[Fax] [nvarchar](20) NULL,
[URL] [nvarchar](250) NULL,
[Logo] [nvarchar](250) NULL,
[UK] [char](1) NULL,
[Eire] [char](1) NULL,
[TGIAccountMgr] [int] NULL,
[StartDate] [datetime] NULL,
[ExitDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Brand] ADD DEFAULT ('N') FOR [UK]
GO
ALTER TABLE [dbo].[Brand] ADD DEFAULT ('N') FOR [Eire]
GO
SELECT COUNT([ID])/62 Completed
FROM [dbo].[SupplierSurveyInput]
where CYearStockist <> 'u'
select
ROW_NUMBER() OVER(order by (((sum(Saleability) + sum(Quality) + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10) / count(*)) desc) as Position,
brandid,
B.Name,
sum(Saleability) /count(*) as avgSaleability,
sum(Quality) /count(*) as avgQuality,
sum(Availability) /count(*) as avgAvailability,
sum(Delivery) /count(*) as avgDelivery,
sum(Returns) /count(*) as avgReturns,
sum(RetailPrice) /count(*) as avgRetailPrice,
sum(ServiceSupport) /count(*) as avgServiceSupport,
sum(Rep) /count(*) as avgRep,
sum(Accounts) /count(*) as avgAccounts,
sum(CreditControl) /count(*) as avgCreditControl,
(((sum(Saleability) + sum(Quality) + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10) / count(*)) Total,
count(*) Stockists,
sum(case when nextyeartrade = 'Y' then 1 else 0 end) as StockistsNY,
C.Completed Completed
from [dbo].[SupplierSurveyInput] S
inner join dbo.Brand B
on S.BrandID = B.ID
Left join(SELECT [SupplierSurveyID], COUNT([ID])/62 Completed FROM [dbo].[SupplierSurveyInput] where CYearStockist <> 'u' group by [SupplierSurveyID]) C on C.SupplierSurveyID = S.SupplierSurveyID
where [CYearStockist] = 'Y'
group by b.name, brandid, S.SupplierSurveyID, C.Completed
order by Total desc
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Brand](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
select
ROW_NUMBER() OVER(order by (((sum(Saleability) + sum(Quality) + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10) / count(*)) desc) as Position,
brandid,
B.Name,
sum(Saleability) /count(*) as avgSaleability,
sum(Quality) /count(*) as avgQuality,
sum(Availability) /count(*) as avgAvailability,
sum(Delivery) /count(*) as avgDelivery,
sum(Returns) /count(*) as avgReturns,
sum(RetailPrice) /count(*) as avgRetailPrice,
sum(ServiceSupport) /count(*) as avgServiceSupport,
sum(Rep) /count(*) as avgRep,
sum(Accounts) /count(*) as avgAccounts,
sum(CreditControl) /count(*) as avgCreditControl,
(((sum(Saleability) + sum(Quality) + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10) / count(*)) Total,
count(*) Stockists,
sum(case when nextyeartrade = 'Y' then 1 else 0 end) as StockistsNY,
C.Completed Completed,
CASE WHEN (count(*)*100)/C.Completed <= 10 then 1
Else 2 END as Tier
from [dbo].[SupplierSurveyInput] S
inner join dbo.Brand B
on S.BrandID = B.ID
Left join(SELECT [SupplierSurveyID], COUNT([ID])/62 Completed FROM [dbo].[SupplierSurveyInput] where CYearStockist <> 'u' group by [SupplierSurveyID]) C on C.SupplierSurveyID = S.SupplierSurveyID
where [CYearStockist] = 'Y'
group by b.name, brandid, S.SupplierSurveyID, C.Completed
order by Tier desc, Total desc
;WITH cteSupplierSurveyInput AS (
SELECT
SupplierSurveyID,
BrandID,
SUM(Saleability) /COUNT(*) AS avgSaleability,
SUM(Quality) /COUNT(*) AS avgQuality,
SUM(Availability) /COUNT(*) AS avgAvailability,
SUM(Delivery) /COUNT(*) AS avgDelivery,
SUM(RETURNS) /COUNT(*) AS avgReturns,
SUM(RetailPrice) /COUNT(*) AS avgRetailPrice,
SUM(ServiceSupport) /COUNT(*) AS avgServiceSupport,
SUM(Rep) /COUNT(*) AS avgRep,
SUM(Accounts) /COUNT(*) AS avgAccounts,
SUM(CreditControl) /COUNT(*) AS avgCreditControl,
(((SUM(Saleability) + SUM(Quality) + SUM(Availability) + SUM(Delivery) + SUM([RETURNS]) + SUM(RetailPrice) + SUM(ServiceSupport) + SUM(Rep) + SUM(Accounts) + SUM(CreditControl) ) /10) / COUNT(*)) Total,
COUNT(*) Stockists,
SUM(CASE WHEN nextyeartrade = 'Y' THEN 1 ELSE 0 END) AS StockistsNY
FROM
SupplierSurveyInput S
GROUP BY
S.BrandID,
S.SupplierSurveyID
),
cteSupplierSurveyCountFiltered AS(
SELECT
SupplierSurveyID,
COUNT(*) AS SupplierSurveyCountFiltered
FROM
SupplierSurveyInput
WHERE
CYearStockist != 'U'
GROUP BY
SupplierSurveyID
),
cteSupplierSurveyCount AS (
SELECT
SupplierSurveyID,
COUNT(*) AS SupplierSurveyCount
FROM
SupplierSurveyInput
GROUP BY
SupplierSurveyID
)
SELECT
ROW_NUMBER() OVER(ORDER BY s.Total) AS RowNumber,
*,
CONVERT(FLOAT,f.SupplierSurveyCountFiltered)/c.SupplierSurveyCount * 100 AS Percentage,
case
when CONVERT(FLOAT,f.SupplierSurveyCountFiltered)/c.SupplierSurveyCount * 100 >= 10 then 1
else 0
end AS PercentageOrder
FROM
cteSupplierSurveyInput s
LEFT JOIN cteSupplierSurveyCountFiltered f ON s.SupplierSurveyID = f.SupplierSurveyID
LEFT JOIN cteSupplierSurveyCount c ON s.SupplierSurveyID = c.SupplierSurveyID
LEFT JOIN Brand b ON s.BrandID = b.ID
ORDER BY
PercentageOrder,
Total
select
ROW_NUMBER() OVER(order by (((sum(Saleability) + sum(Quality) + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10) / count(*)) desc) as Position,
brandid,
B.Name,
sum(Saleability) /count(*) as avgSaleability,
sum(Quality) /count(*) as avgQuality,
sum(Availability) /count(*) as avgAvailability,
sum(Delivery) /count(*) as avgDelivery,
sum(Returns) /count(*) as avgReturns,
sum(RetailPrice) /count(*) as avgRetailPrice,
sum(ServiceSupport) /count(*) as avgServiceSupport,
sum(Rep) /count(*) as avgRep,
sum(Accounts) /count(*) as avgAccounts,
sum(CreditControl) /count(*) as avgCreditControl,
(((sum(Saleability) + sum(Quality) + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10) / count(*))*20 Total,
count(*) Stockists,
sum(case when nextyeartrade = 'Y' then 1 else 0 end) as StockistsNY,
C.Completed Completed,
CASE WHEN (count(*)*100)/C.Completed <= 10 then 1
Else 2 END as Tier
from [dbo].[SupplierSurveyInput] S
inner join dbo.Brand B
on S.BrandID = B.ID
Left join(SELECT [SupplierSurveyID], COUNT([ID])/COUNT(DISTINCT(BrandID)) Completed FROM [dbo].[SupplierSurveyInput] where CYearStockist <> 'u' group by [SupplierSurveyID]) C on C.SupplierSurveyID = S.SupplierSurveyID
where [CYearStockist] = 'Y' AND S.SupplierSurveyID = 1 AND CountryID in (3)
group by b.name, brandid, S.SupplierSurveyID, C.Completed
order by Tier desc, Total desc
ROW_NUMBER() OVER(order by (((sum(Saleability) + sum(Quality) + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10) / count(*)) desc) as Position,
what is the result now and what you wish to have?
if you can send some sample \example with some create table\populate this table with some data and your select -- it could help too