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

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.

Please advise the best way to do this.

Thank you
0
garethtnash
Asked:
garethtnash
  • 2
  • 2
  • 2
2 Solutions
 
Jesus RodriguezIT 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
 
x-menIT 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
 
Jesus RodriguezIT 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
garethtnashAuthor Commented:
Sorry All, none of these work;

Answer 1 results in

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


Answer 2 Results in

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

Open in new window


I get comp as '0'

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

Answer 3 Results in

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

Any suggestions please?

Thank you
0
 
x-menIT super heroCommented:
In answer 2, you have to replace "table" with your actual table name.
0
 
garethtnashAuthor 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now