No Cursor

How would I take the total of the failures column below so I can do %'s of
the total. I can do it with an ADO recordset from VB, but I'm trying to do
it in TSQL 1st.
Thanks
Paul
DECLARE @.BeginDate DateTime
DECLARE @.EndDate DateTime
Set @.BeginDate = '2005-05-12'
Set @.EndDate = '2005-05-16'
Select Top 5
Dept,
SubString(Category + '-' + SubCategory,0, 50) as 'Failure',
Sum(NumFailures) as 'Failures'
From DBO.FTC_TESTRESULTS
Where Dept = '6600'
AND TestDate Between @.BeginDate AND @.EndDate
GROUP BY SubString(Category + '-' + SubCategory,0, 50),Dept
ORDER BY Dept, Sum(NumFailures) DESC
Dept Failure
Failures PCT
-- ---- --
6600 Misc-No Speed Sensor Signal 28
28/70
6600 Seam Leaks-Rear Retainer to Rear Case 21
21/70
6600 Noise-Neutral
10 10/70
6600 Misc-Stud/Bolt Missing 6
ETC
6600 Noise-All Modes
5
Total
70SELECT TOP 5
dept,
SUBSTRING(category + '-' + subcategory,0, 50) AS 'Failure',
SUM(numfailures) AS 'Failures',
SUM(numfailures) /
(SELECT CAST(SUM(numfailures) AS REAL)
FROM dbo.FTC_TESTRESULTS)
FROM dbo.FTC_TESTRESULTS
WHERE dept = '6600'
AND testdate BETWEEN @.begindate AND @.enddate
GROUP BY SUBSTRING(category + '-' + subcategory,0, 50),dept
ORDER BY dept, SUM(numfailures) DESC
David Portas
SQL Server MVP
--|||Dave,
Will I need to further narrow the divisor... I want to divide by 70...
the total of the top 5, it is currently dividing by the entire population .
Bottom of post is the original code that returns top 5 with no divisor.
dept Failure
Failures
-- ---- -- --
--
6600 Misc-No Speed Sensor Signal 28
8.5995086E-3
6600 Seam Leaks-Rear Retainer to Rear Case 21
6.4496314E-3
6600 Noise-Neutral 10
3.071253E-3
6600 Misc-Stud/Bolt Missing 6
1.8427519E-3
6600 Noise-All Modes 5
1.5356265E-3

> SELECT TOP 5
> dept,
> SUBSTRING(category + '-' + subcategory,0, 50) AS 'Failure',
> SUM(numfailures) AS 'Failures',
> SUM(numfailures) /
> (SELECT CAST(SUM(numfailures) AS REAL)
> FROM dbo.FTC_TESTRESULTS)
> FROM dbo.FTC_TESTRESULTS
> WHERE dept = '6600'
> AND testdate BETWEEN @.begindate AND @.enddate
> GROUP BY SUBSTRING(category + '-' + subcategory,0, 50),dept
> ORDER BY dept, SUM(numfailures) DESC
> --
> David Portas
> SQL Server MVP
> --
>
Select Top 5
Dept,
SubString(Category + '-' + SubCategory,0, 50) as 'Failure',
Sum(NumFailures) as 'Failures'
From DBO.FTC_TESTRESULTS
Where Dept = '6600'
AND TestDate Between @.BeginDate AND @.EndDate
GROUP BY SubString(Category + '-' + SubCategory,0, 50),
Dept
ORDER BY Dept, Sum(NumFailures) DESC|||On Wed, 25 May 2005 20:25:51 -0400, Paul Ilacqua wrote:

>Dave,
> Will I need to further narrow the divisor... I want to divide by 70...
>the total of the top 5, it is currently dividing by the entire population .
>Bottom of post is the original code that returns top 5 with no divisor.
Hi Paul,
Try this one. I couldn't test it, since you didn't provide CREATE TABLE
and INSERT statements (see www.aspfaq.com/5006).
SELECT TOP 5 Dept,
SUBSTRING(Category + '-' + SubCategory, 0, 50) AS Failure,
SUM(NumFailures) AS Failures,
SUM(NumFailures) /
(SELECT SUM(Failures)
FROM (SELECT TOP 5 SUM(NumFailures) AS Failures
FROM dbo.FTC_TestResults
WHERE Dept = '6600'
AND TestDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY SUBSTRING(Category + '-' + SubCategory, 0, 50)
ORDER BY SUM(NumFailures) DESC) AS d) AS Pct
FROM dbo.FTC_TestResults
WHERE Dept = '6600'
AND TestDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY SUBSTRING(Category + '-' + SubCategory,0, 50)
ORDER BY SUM(NumFailures) DESC
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Works perfectly... and I spent the last couple of days figuring out how
it works. Now I think I got it. Thanks again.
Paul
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9mse911404i9c32u9cak1o4e40gl16t6b7@.
4ax.com...
> On Wed, 25 May 2005 20:25:51 -0400, Paul Ilacqua wrote:
>
> Hi Paul,
> Try this one. I couldn't test it, since you didn't provide CREATE TABLE
> and INSERT statements (see www.aspfaq.com/5006).
> SELECT TOP 5 Dept,
> SUBSTRING(Category + '-' + SubCategory, 0, 50) AS Failure,
> SUM(NumFailures) AS Failures,
> SUM(NumFailures) /
> (SELECT SUM(Failures)
> FROM (SELECT TOP 5 SUM(NumFailures) AS Failures
> FROM dbo.FTC_TestResults
> WHERE Dept = '6600'
> AND TestDate BETWEEN @.BeginDate AND @.EndDate
> GROUP BY SUBSTRING(Category + '-' + SubCategory, 0, 50)
> ORDER BY SUM(NumFailures) DESC) AS d) AS Pct
> FROM dbo.FTC_TestResults
> WHERE Dept = '6600'
> AND TestDate BETWEEN @.BeginDate AND @.EndDate
> GROUP BY SUBSTRING(Category + '-' + SubCategory,0, 50)
> ORDER BY SUM(NumFailures) DESC
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)