garethtnash

asked on

# Tier Query Results MS SQL

Hello Experts,

I have a query, which returns results from an online survey, the existing query is detailed below -

What I would like to do though is tier the results, so that any suppliers with less that 10% stockists are moved to the bottom, so the results would look like so

Stockists > 10% order by Total Desc

Stockists < 10% order by Total Desc

and have these returned in one recordset as they are currently.

Any suggestions?

Appreciate the help, many thanks

I have a query, which returns results from an online survey, the existing query is detailed below -

```
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
```

What I would like to do though is tier the results, so that any suppliers with less that 10% stockists are moved to the bottom, so the results would look like so

Stockists > 10% order by Total Desc

Stockists < 10% order by Total Desc

and have these returned in one recordset as they are currently.

Any suggestions?

Appreciate the help, many thanks

What you need to do is add another column e.g. StockistPercentage and use CASE

when Stockists >= 10% then 1

when Stockists < 10% then 2

and do the Order By StockistPercentage, Total Desc

if you need further help to calculate percentage then let me know

when Stockists >= 10% then 1

when Stockists < 10% then 2

and do the Order By StockistPercentage, Total Desc

if you need further help to calculate percentage then let me know

ASKER

Thank you Both,

My tables that form the query look like this --

And

I can query the table using the query above, and I can query the table like so -

To get the total number of people that participated in the survey.

I'm stuck as to how to marry these two together?

Thanks

My tables that form the query look like this --

```
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
```

And

```
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
```

I can query the table using the query above, and I can query the table like so -

```
SELECT COUNT([ID])/62 Completed
FROM [dbo].[SupplierSurveyInput]
where CYearStockist <> 'u'
```

To get the total number of people that participated in the survey.

I'm stuck as to how to marry these two together?

Thanks

Can you also provide some sample data for both files in excel table format

thanks

thanks

ASKER

Hi RehanYousaf,

I think I'm a stage closer on this, using the query below, what's the best way of including your CASE statement?

The query below adds a column that returns the total number of survey participants...

The data is slightly complicated, but I'll see what I can do :)

I think I'm a stage closer on this, using the query below, what's the best way of including your CASE statement?

The query below adds a column that returns the total number of survey participants...

```
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
```

The data is slightly complicated, but I'll see what I can do :)

If you have to calculate Stockists %, how will you do it

based on that I can provide you with the code

Also, why are you hard coding 62 in your code

based on that I can provide you with the code

Also, why are you hard coding 62 in your code

ASKER

Here's the main table data, for the brand table, please remove all but the ID and Name columns for the DDL, so --

And use the Brands spreadsheet attached ....

Thank you :)SupplierSurveySample-Data.xlsxBrand-Sample-Data.xlsx

```
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
```

And use the Brands spreadsheet attached ....

Thank you :)SupplierSurveySample-Data.xlsxBrand-Sample-Data.xlsx

Thanks for the data, just need a little more explanation in simple term that

what Stockists is?, and

to calculate Stockists percentage from the data provided in excel how will I do it using simple maths?

what Stockists is?, and

to calculate Stockists percentage from the data provided in excel how will I do it using simple maths?

ASKER

Thanks RehanYousaf, ok to explain, we are a buying group for a number of retailers (stockists) each year we ask them to complete a survey on the brands that they stock, for the data returned the 'Completed' column is the total number of retailers that completed the survey, 'Stockists' is the number of retailers that stocked the brand, as they don't stock all brands.

As we don't want the figures to be offset by a brand that has a very a single very happy stockist and no other stockists. we have decided to move all brands where less (or equal) than 10% of the the total 'completed' are stockists to the bottom and then apply the normal order by.

So in Excel, the query would be = Sum('Stockists'/'Completed')*100, when i tried this in SQL, I got strange results....

Hope this helps.

62 was hard coded, as we know that to be the total brands this year, although if this could be queried as COUNT DISTINCT brandid that would be good also....

Thank you so much.

G

As we don't want the figures to be offset by a brand that has a very a single very happy stockist and no other stockists. we have decided to move all brands where less (or equal) than 10% of the the total 'completed' are stockists to the bottom and then apply the normal order by.

So in Excel, the query would be = Sum('Stockists'/'Completed

Hope this helps.

62 was hard coded, as we know that to be the total brands this year, although if this could be queried as COUNT DISTINCT brandid that would be good also....

Thank you so much.

G

Your calculations are fine, just need to change

COUNT([ID])/62 Completed

to

CONVERT(FLOAT, COUNT([ID]))/62 Completed

and case statement will be something like

CASE

WHEN c.Completed >= 0.10 THEN 1

ELSE 0

END AS PercentageOrder

COUNT([ID])/62 Completed

to

CONVERT(FLOAT, COUNT([ID]))/62 Completed

and case statement will be something like

CASE

WHEN c.Completed >= 0.10 THEN 1

ELSE 0

END AS PercentageOrder

ASKER

Thank you, :)

I think we are almost there,.. I had just put the following together --

Which appears to work, but it still has the hard coded 62 in it, any suggestions on the best way to not have this hardcoded?

Again, thank you so much for all your help.

G

I think we are almost there,.. I had just put the following together --

```
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
```

Which appears to work, but it still has the hard coded 62 in it, any suggestions on the best way to not have this hardcoded?

Again, thank you so much for all your help.

G

Anotehr way of doing this will be

```
;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
```

ASKER

Thank you,. but that gives me some strange results... One question, the query below works great now, with the exception of the 'Position' column, which is no longer correct,..

Any thoughts? I know that

is no longer correct as it doesn't take into consideration the tier element... just not sure how to correct this?

Thanks

G

```
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
```

Any thoughts? I know that

`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, `

is no longer correct as it doesn't take into consideration the tier element... just not sure how to correct this?

Thanks

G

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

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

ASKER

Close, but not quite, this starts 1 at the first result with a tier of 1, which means that the last 19 have a position of the first 19...

Thanks

Thanks

ASKER

Thank you so much for your support, for your reference, the last query was just missing the DESC!

Thank you Thank you and Happy New Year

Thank you Thank you and Happy New Year

Sorry I missed one as well

use

CONVERT(FLOAT,(COUNT(*))*100)/C.Completed

instead of

(COUNT(*)*100)/C.Completed

in your script

use

CONVERT(FLOAT,(COUNT(*))*1

instead of

(COUNT(*)*100)/C.Completed

in your script

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