Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Select Percentage Completed MS SQL

Posted on 2012-09-04
Medium Priority
550 Views
Hello,

I have a table that has the following columns -

[ID], [SupplierSurveyID], [BrandID], [PartnerID], [CYearStockist],[Completed]

Partners (PartnerID) select the Brands (BrandID) that they stock and then Complete (Completed) a survey for each brand stocked.

What I want to be able to do in a single select statement is

Count all brands stocked (A)

so where SupplierSurveyID = 1 AND PartnerID = 440 AND CYearStockist = 'Y' ((PLUS 2))

AND

Count all brands stocked where Completed = 'Y' (B)

so where SupplierSurveyID = 1 AND PartnerID = 440 AND CYearStockist = 'Y' AND Completed = 'Y' ((PLUS 1))

And then divide (B) by (A) to return the Percentage completed.

Thank you
0
Question by:garethtnash
• 2
• 2
• 2

LVL 13

Expert Comment

ID: 38364563
SELECT BRANCDID
,A
,B
,A/B AS C
FROM (
SELECT [BrandID]
,COUNT(CASE [BRANDID]
WHEN 'A' THEN 1
ELSE 0 END AS SUM_A) AS A
,COUNT(CASE [Completed]
WHEN 'Y' THEN 1
ELSE 0 END AS SUM_B) AS B
WHERE SupplierSurveyID = 1 AND PartnerID = 440 AND CYearStockist = 'Y'
GROUP BY [BrandID]) D
0

LVL 18

Assisted Solution

x-men earned 1000 total points
ID: 38364565
SELECT
(SELECT Count(*) from Table where SupplierSurveyID = 1 AND PartnerID = 440 AND CYearStockist = 'Y' AND Completed = 'Y' ((PLUS 1)))/(SELECT Count(*) from Table where SupplierSurveyID = 1 AND PartnerID = 440 AND CYearStockist = 'Y' ((PLUS 2)))
0

LVL 13

Accepted Solution

Jesus Rodriguez earned 1000 total points
ID: 38364608
I THINK THAT I MADE A MISTAKE, REPLACE THE COUNT BY THE SUM FUNCTION

SELECT BRANCDID
,A
,B
,A/B AS C
FROM (
SELECT [BrandID]
,SUM(CASE [BRANDID]
WHEN 'A' THEN 1
ELSE 0 END AS SUM_A) AS A
,SUM(CASE [Completed]
WHEN 'Y' THEN 1
ELSE 0 END AS SUM_B) AS B
WHERE SupplierSurveyID = 1 AND PartnerID = 440 AND CYearStockist = 'Y'
GROUP BY [BrandID]) D
0

Author Comment

ID: 38366996
Sorry All, none of these work;

Msg 195, Level 15, State 10, Line 7
'COUNT' is not a recognized built-in function name.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Table'.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Table'.

If i replace both table with dbo.SupplierSurveyInput then I get

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

I'm asuming that it doesn't like the PLUS 1 & PLUS 2, if i remove these and bracketsand change to -

``````SELECT
(SELECT (Count(ID)+1) from dbo.SupplierSurveyInput where SupplierSurveyID = 1 AND PartnerID = 440 AND CYearStockist = 'Y' AND Completed = 'Y')/(SELECT (Count(ID)+2) from dbo.SupplierSurveyInput where SupplierSurveyID = 1 AND PartnerID = 440 AND CYearStockist = 'Y')
As Comp
``````

I get comp as '0'

But if i run the select statements seperately I get '2' and '12', so comp should not be '0'?

Msg 195, Level 15, State 10, Line 7
'SUM' is not a recognized built-in function name.

Thank you
0

LVL 18

Expert Comment

ID: 38368593
In answer 2, you have to replace "table" with your actual table name.
0

Author Closing Comment

ID: 38403197
Thank you
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
###### Suggested Courses
Course of the Month20 days, 16 hours left to enroll