Streamline this SQL 1

[ready4data]

Mainly an MS Access user I need to start doing things in SQL Server.
After much tweaking I came up with this sql. It works but I want to see if there is a better way to write this.

SELECT
dbo.Training2_V.LBU, dbo.Training2_V.District,
Count(dbo.Training2_V.SRNumber) AS Calls,
Sum(CASE WHEN dbo.Training2_V.Trained = 'Yes' THEN 1 ELSE 0 END) AS Trained,
Convert(FLOAT(53),Round(Cast(SUM(CASE WHEN dbo.Training2_V.Trained = 'Yes' THEN 1 ELSE 0 END) AS DECIMAL(10, 4)) / Count(dbo.Training2_V.SRNumber), 3)) AS PCT,
Convert(FLOAT(53),Round(Cast(SUM(CASE WHEN dbo.Training2_V.Trained = 'Yes' THEN 1 ELSE 0 END) AS DECIMAL(10, 4)) * 100 / Count(dbo.Training2_V.SRNumber), 0)) AS Graph,
dbo.Training2_V.Quarter
FROM
dbo.Training2_V
GROUP BY
dbo.Training2_V.LBU,
dbo.Training2_V.District,
dbo.Training2_V.Quarter

Thanks,
Scott

 

[r937]

SELECT LBU
     , District
     , Quarter
     , Calls
     , Trained
     , ROUND(1.0 * Trained / Calls, 3) AS PCT
     , 100 * Trained / Calls AS Graph
  FROM (
       SELECT LBU
            , District
            , Quarter
            , COUNT(SRNumber) AS Calls
            , Sum(CASE WHEN Trained = 'Yes'
                      THEN 1 ELSE 0 END) AS Trained
         FROM dbo.Training2_V
       GROUP 
           BY LBU
            , District
            , Quarter
       ) AS d

i'm pretty sure that i don't really understand what you're doing in the PCT and Graph calcualtions, but i'm confident that this query is a lot more streamlined than what you had ;-)

r937.com | rudy.ca

 

[SQLSister]

BTW you never ever want to use float datatypes if you aredoing calculations. They are not exact numbers and rounding issues will creep in.

ROUND(1.0 * Trained / Calls, 3)

The reason why Rudy multiplies by 1.0 is so that SQl Server will not do integer math in the division. For instance if trained = 11 and calls = 30, then using integer math (which SQL server would do if both number are integers) the result would be 0 whare you probably really want .367 as the result. This is techinque you should use anytime you do a division where the numbers input into the division are integers.

"NOTHING is more important in a database than integrity." ESquared

 

[ready4data]

Thank you both for the sql and explaination. I have several others that I will modify using this format.
Scott