• Status: Solved
• Priority: Medium
• Security: Public
• Views: 555

# Select Percentage Completed MS SQL

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
garethtnash
• 2
• 2
• 2
2 Solutions

IT ManagerCommented:
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

IT super heroCommented:
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

IT ManagerCommented:
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 Commented:
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

IT super heroCommented:
In answer 2, you have to replace "table" with your actual table name.
0

Author Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.