Re: Is There Easier Way to Calculate This Percentage in T-SQL?

From: jj (jjj.nosspam_at_mindspring.com)
Date: 05/30/04 

Date: Sun, 30 May 2004 13:39:30 GMT

David, thanks VERY MUCH for the tips. I see the advantage of COUNT over
SUM. I will try this. However, if I might ask a few more questions.

1. In order to account for "division by zero", shouldn't I start my SQL
with something like this (and I usually use the "convert(decimal, ...)"
when doing division). Does this syntax look OK?

SELECT CASE WHEN (countvar5 + countvar6) = 0 THEN 0
             ELSE convert(decimal, (countvar1 + countvar2 + ....) /
                                   (countvar5 + countvar6))
   FROM
     (SELECT
        COUNT(CASE....(continued as indicated in your example below)

2. Will this one large SQL statement also provide the 6 values I need
for CountVar1 through CountVar6? In other words, using this "nested"
style, will I no longer need to have 6 seperate COUNT(CASE....)
statements, IN ADDITION TO the one you mentioned below, to get those 6
values for CountVar1 to CountVar6? If so, that will ensure SQL Server
is not repeating the calculations, as in my original code, just to get
the percentage, right?

3. Would storing these SQL statements in a STORED PROCEDURE make things
easier and/or faster? The only issue is that with all my SQL/queries, I
have DATE parameters. I know you can use parameters in STORED
PROCEDURES, but not sure if that's the best way to go here, or worth trying.

Thanks very much for your help & advice. I really appreciate your help
and expertise.
Gary.
==================
David Portas wrote:
> If you nest your query as a derived table then you can reference the
> calculated columns by their names:
>
> SELECT (countvar1 + countvar2 + ... ) / (countvar5 + countvar6)
> FROM
> (SELECT
> COUNT(CASE
> WHEN sale_date BETWEEN '#date1#' AND '#date2#'
> AND (this_var = 'Y' OR that_var = 'Y')
> AND other_var = ' '
> THEN 1 END) AS countvar1,
> COUNT(CASE
> WHEN sale_date BETWEEN '#date3#' AND '#date4#'
> AND (this_var = 'Y' OR that_var = 'Y')
> AND other_var = ' '
> THEN 1 END) AS countvar2,
> ...
> FROM YourTable) AS T
>
> In this query COUNT has the advantage over SUM that it will return 0 rather
> than NULL if no rows match the CASE statement. That way you don't need the
> ELSE clause.
>

Relevant Pages

  • Re: Optimising the Query
    ... Slightly reformatting your SQL statement: ... SUM (CASE WHEN ... Do you need the outer join? ... Here's the explain plan generated by the query: ...
    (comp.databases.oracle.misc)
  • Re: Optimising the Query
    ... Slightly reformatting your SQL statement: ... SUM (CASE WHEN ... Do you need the outer join? ... Here's the explain plan generated by the query: ...
    (comp.databases.oracle.misc)
  • Re: Help with an SQL Statement plzzzzz
    ... I would like an sql statement that count the sum of the phone number that appears more than once only. ... I've called the source table tbname. ... SELECT tbname.name, tbname.volumes, CountAS [Total repeat Volumes by name] INTO tb2 ...
    (microsoft.public.access.modulesdaovba)
  • Re: Text box value based on sum of another text box
    ... What happens is Access creates an SQL statement it passes to Jet. ... "T: CommandeIngenierie" ... Variable to be calculated from sum of Value variable ...
    (microsoft.public.access.forms)
  • RE: Help with an SQL Statement plzzzzz
    ... statement that count the sum of the phone number that appears more than once ... I've called the source table tbname. ... Here's the statement that create tb2 where you'll find Total repeat Volumes ... SQL Statement will produce. ...
    (microsoft.public.access.modulesdaovba)