11
\$\begingroup\$

I put together this simple query which calculates the total percentage of answered questions and unanswered questions. The formulas used to calculate these are shown below:

$$ \text{Answered Percentage} = 100 - \left(\frac{\text{Unanswered}}{\text{Answered}} \times 100\right) \\ \text{Unanswered Percentage} = \frac{\text{Unanswered}}{\text{Answered}} \times 100 $$

Finally, here's the source:

DECLARE @TotalQuestions DECIMAL = (
  SELECT COUNT(*) FROM Posts
);

DECLARE @UnansweredQuestions DECIMAL = (
  SELECT COUNT(*) FROM Posts WHERE AnswerCount = 0
);

-- The percentage of un-answered questions. Calculated
-- using this:
  -- (Unanswered Questions / Total Site Questions) * 100
SELECT 
  (@UnansweredQuestions / @TotalQuestions) * 100.0
AS [Percentage Of Unanswered Questions];

-- The percentage of answered questions. Calculated
-- using this:
  -- 100 - ((Unanswered Questions / Total Site Questions) * 100)
SELECT
  100 - ((@UnansweredQuestions / @TotalQuestions) * 100.0)
AS [Perentage Of Answered Questions];
\$\endgroup\$
5
  • 3
    \$\begingroup\$ Any reason for not addressing questions that are in a non-responsive state (closed/on-hold)? \$\endgroup\$
    – Snowhawk
    Jul 8, 2015 at 20:52
  • \$\begingroup\$ @Snowhawk04 No, not really. I didn't think of that until after I posted. I'll probably post another question once I have changed it and added improvements from answers. \$\endgroup\$ Jul 8, 2015 at 20:53
  • \$\begingroup\$ @Quill Yup. I created this query specifically for Math.SE. \$\endgroup\$ Jul 8, 2015 at 22:49
  • 1
    \$\begingroup\$ In your description you say Unanswered Percentage = (Unanswered / Answered) * 100. I think you mean Unanswered Percentage = (Unanswered / Total) * 100, which would match your source. (And similarly for the other formula in your description.) \$\endgroup\$
    – mathmandan
    Jul 9, 2015 at 6:12
  • \$\begingroup\$ I agree with @mathmandan. These percentage calculations are wrong. \$\endgroup\$
    – nhgrif
    Jul 9, 2015 at 13:04

2 Answers 2

7
\$\begingroup\$

Comments

Your comments are mostly clutter, and I mean that because your naming is good enough to where the comments are mostly irrelevant. Also, if you do need to make multi-line comment, you can use the same syntax in SQL as in pretty much everything else.

Look at this, the comments and the code read almost identically:

/**
 * The percentage of un-answered questions. Calculated
 * using this:
 * (Unanswered Questions / Total Site Questions) * 100
 */
SELECT 
  (@UnansweredQuestions / @TotalQuestions) * 100.0 AS [Percentage Of Unanswered Questions];

There is an elegant way of assigning an alias in an expression that would make it even easier to read:

SELECT 
  [Percentage Of Unanswered Questions] = (@UnansweredQuestions / @TotalQuestions) * 100.0

Does exactly the same thing, but reads more like typical code, where you name a thing before assigning it something, rather than after.


Dead code

Your first SELECT query is never displayed. There are two ways you could fix that, either by making them two different columns, or two different rows (according to your needs).

Columns:

SELECT 
  [Percentage Of Unanswered Questions] = (@UnansweredQuestions / @TotalQuestions) * 100.0
, [Perentage Of Answered Questions] = 100 - ((@UnansweredQuestions / @TotalQuestions) * 100.0);

columns

Rows:

SELECT 
  [Type of value] = 'Percentage Of Unanswered Questions'
  , [Value] = (@UnansweredQuestions / @TotalQuestions) * 100.0
UNION
SELECT
  'Percentage Of Answered Questions'
  , 100 - ((@UnansweredQuestions / @TotalQuestions) * 100.0);

rows

\$\endgroup\$
5
\$\begingroup\$

Your code is formatted well, and has good comments, but:

  • SELECT COUNT(*) FROM Posts WHERE AnswerCount = 0: as @Snowhawk04 pointed out, this also selects closed questions. AND ClosedDate IS null usually works.
  • I don't see why you don't join the two SELECT statements into one SELECT.
  • Your formula displayed in the question, you don't divide the Unanswered Qs by the Answered Qs, you divide Unanswered Qs by Total Qs.
  • You don't need AS statements with or without them provides the same functionality, they're kinda cluttery.
  • Rather than subtracting \$100\$ from the unanswered percentage, simply generate the Answered Qs by subtracting the Unanswered Qs from the Total Qs and use the same formula for Unanswered Qs, like so: ((@TotalQuestions - @UnansweredQuestions) / @TotalQuestions) * 100.0
\$\endgroup\$
1
  • \$\begingroup\$ About your first point, semicolons should be used between each declaration. In every other DBMS you'd get a syntax error without them, Microsoft's SQL plays pretty loose with the syntax. \$\endgroup\$
    – Phrancis
    Jul 8, 2015 at 22:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.