This query seems to have way too many parentheses.

CAST(ISNULL(ROUND(CAST((SUM(product1) + SUM(product2)) AS FLOAT) / CAST(salescalls.visits AS FLOAT) * 100, 2), 0) AS VARCHAR) + '%' AS avrg
FROM sales, salescalls
WHERE sales.salescallId = salescalls.id

There is something you can do about

CAST((SUM(product1) + SUM(product2)) AS FLOAT) / CAST(salescalls.visits AS FLOAT) * 100

To perform floating-point division rather than integer division, either the dividend or the divisor needs to be a float. You don't need make both of operands floats. Furthermore, you can make a the dividend a float by using floating-point multiplication. That means that you don't need to cast anything to a float. That simplifies it to

100.0 * (SUM(product1) + SUM(product2)) / salescalls.visits

Next, you can rewrite the sums as one aggregate by adding the two columns in each row before summing the rows:

100.0 * SUM(product1 + product2) / salescalls.visits

I don't think that there's not much that can be done about ROUND(), ISNULL(), and CAST(… AS VARCHAR), though. Consider doing that formatting in your application-layer code instead of SQL.

You've written the query using an old-style join. It would be better to write it using a JOIN keyword:

                   100.0 * SUM(product1 + product2) / salescalls.visits,
           ) AS VARCHAR
       ) + '%' AS avrg
    FROM sales INNER JOIN salescalls ON sales.salescallId = salescalls.id
  • \$\begingroup\$ I agree with the old-style join being ugly. That's how the queries I'm working with are written so I just stick with it. \$\endgroup\$
    – user30586
    Aug 29 '14 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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