Is it possible to sort by Group Totals

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Is it possible to sort by Group Totals

Author

Topic

Spica66
Starting Member

23 Posts

Posted - 2012-08-06 : 11:11:02

I have the following query in a stored procedure:

ALTER PROCEDURE [dbo].[frc_Top25byFRCcode]

@startDate AS DATETIME = null,
@endDate AS DATETIME = null,
@NFClaimStatus int = null

AS

DECLARE @SD DATETIME
DECLARE @ED DATETIME
DECLARE @NFCS INT

SET @SD = @startDate
SET @ED = @endDate
SET @NFCS = @NFClaimStatus

BEGIN
SELECT
w.NFWARRANTYPARTID + ' - ' + p.NFWARRANTYPARTDESCRIPTION AS WCCDESCRIPTION,

((SELECT ROUND(ISNULL(SUM(PRICE*QTY),0),2)
FROM NFWARRANTYCLAIMSITEMS i
WHERE i.CLAIMID = w.CLAIMID AND i.CORRECTIONCODE = w.CORRECTIONCODE)+
(SELECT ROUND(ISNULL(SUM(AMOUNT),0),2)
FROM NFWARRANTYMISCCOSTS m
WHERE m.CLAIMID = w.CLAIMID AND m.CORRECTIONCODE = w.CORRECTIONCODE))
+ (W.HOURS*W.PRICE) as AMOUNT

FROM dbo.NFWARRANTYCLAIMS AS c

INNER JOIN (
SELECT NFCLAIMID,
MAX(TRANSDATE) AS TRANSDATE
FROM NFWARRANTYCLAIMHISTORY
WHERE NFCLAIMSTATUS = @NFCS AND (transDate BETWEEN @SD AND @ED)
GROUP BY NFCLAIMID
) AS h ON h.NFCLAIMID = c.CLAIMID

INNER JOIN NFWARRANTYTABLE AS t ON t.WARRANTYNUMBER = c.WARRANTYNUMBER

INNER JOIN (
SELECT *
FROM NFWARRANTYCORRECTIONTABLE
WHERE NFCORRECTIONSTATUS <> 7
)
AS w ON w.CLAIMID = h.NFCLAIMID

INNER JOIN NFWARRANTYPARTS AS p ON p.NFWARRANTYPARTID = w.NFWARRANTYPARTID

ORDER BY w.WCCDESCRIPTION

END

It generates the table shown below:

WCC Description Amount

02.09.07 - Tires 170.00
02.09.07 - Tires 350.00
02.09.07 - Tires 285.00
02.09.07 - Tires 190.00
03.19.08 - Paint 130.00
03.19.08 - Paint 280.00
03.19.08 - Paint 285.00
06.01.05 - Sink 150.00
06.01.05 - Sink 250.00
06.01.05 - Sink 85.00
06.01.05 - Sink 150.00

I am trying to get the SQL statement to SUM the amounts for the grouped WCC Description and sort based on the totals for the group, highest total first:

02.09.07 - Tires 805.00
03.19.08 - Paint 695.00
06.01.05 - Sink 635.00

I thought I could SUM(AMOUNT) and GROUP BY(WCCDESCRIPTION), but I can't figure out where in my code to do those two things. No matter what combination I try, it gives me a Syntax Error.

ALTER PROCEDURE [dbo].[frc_Top25byFRCcode]

@startDate AS DATETIME = null,
@endDate AS DATETIME = null,
@NFClaimStatus int = null

AS

DECLARE @SD DATETIME
DECLARE @ED DATETIME
DECLARE @NFCS INT

SET @SD = @startDate
SET @ED = @endDate
SET @NFCS = @NFClaimStatus

BEGIN
SELECT
w.NFWARRANTYPARTID + ' - ' + p.NFWARRANTYPARTDESCRIPTION AS WCCDESCRIPTION,

Select SUM(Amount) From

(((SELECT ROUND(ISNULL(SUM(PRICE*QTY),0),2)
FROM NFWARRANTYCLAIMSITEMS i
WHERE i.CLAIMID = w.CLAIMID AND i.CORRECTIONCODE = w.CORRECTIONCODE)+
(SELECT ROUND(ISNULL(SUM(AMOUNT),0),2)
FROM NFWARRANTYMISCCOSTS m
WHERE m.CLAIMID = w.CLAIMID AND m.CORRECTIONCODE = w.CORRECTIONCODE))
+ (W.HOURS*W.PRICE) as AMOUNT) x

FROM dbo.NFWARRANTYCLAIMS AS c

INNER JOIN (
SELECT NFCLAIMID,
MAX(TRANSDATE) AS TRANSDATE
FROM NFWARRANTYCLAIMHISTORY
WHERE NFCLAIMSTATUS = @NFCS AND (transDate BETWEEN @SD AND @ED)
GROUP BY NFCLAIMID
) AS h ON h.NFCLAIMID = c.CLAIMID

INNER JOIN NFWARRANTYTABLE AS t ON t.WARRANTYNUMBER = c.WARRANTYNUMBER

INNER JOIN (
SELECT *
FROM NFWARRANTYCORRECTIONTABLE
WHERE NFCORRECTIONSTATUS <> 7
)
AS w ON w.CLAIMID = h.NFCLAIMID

INNER JOIN NFWARRANTYPARTS AS p ON p.NFWARRANTYPARTID = w.NFWARRANTYPARTID

GROUP BY w.WCCDESCRIPTION

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 11:19:40

[code]
ALTER PROCEDURE [dbo].[frc_Top25byFRCcode]

@startDate AS DATETIME = null,
@endDate AS DATETIME = null,
@NFClaimStatus int = null

AS

DECLARE @SD DATETIME
DECLARE @ED DATETIME
DECLARE @NFCS INT

SET @SD = @startDate
SET @ED = @endDate
SET @NFCS = @NFClaimStatus

BEGIN
SELECT
w.NFWARRANTYPARTID + ' - ' + p.NFWARRANTYPARTDESCRIPTION AS WCCDESCRIPTION,
SUM(ROUND(ISNULL(i.PRICE*i.QTY,0),2)
+
ROUND(ISNULL(AMOUNT,0),2)
+ (W.HOURS*W.PRICE)) as AMOUNT
FROM dbo.NFWARRANTYCLAIMS AS c

INNER JOIN (
SELECT NFCLAIMID,
MAX(TRANSDATE) AS TRANSDATE
FROM NFWARRANTYCLAIMHISTORY
WHERE NFCLAIMSTATUS = @NFCS AND (transDate BETWEEN @SD AND @ED)
GROUP BY NFCLAIMID
) AS h ON h.NFCLAIMID = c.CLAIMID

INNER JOIN NFWARRANTYTABLE AS t ON t.WARRANTYNUMBER = c.WARRANTYNUMBER

INNER JOIN (
SELECT *
FROM NFWARRANTYCORRECTIONTABLE
WHERE NFCORRECTIONSTATUS <> 7
)
AS w ON w.CLAIMID = h.NFCLAIMID

INNER JOIN NFWARRANTYPARTS AS p ON p.NFWARRANTYPARTID = w.NFWARRANTYPARTID
INNER JOIN NFWARRANTYCLAIMSITEMS i
ON i.CLAIMID = w.CLAIMID
AND i.CORRECTIONCODE = w.CORRECTIONCODE
INNER JOIN NFWARRANTYMISCCOSTS m
ON m.CLAIMID = w.CLAIMID
AND m.CORRECTIONCODE = w.CORRECTIONCODE
GROUP BY w.NFWARRANTYPARTID + ' - ' + p.NFWARRANTYPARTDESCRIPTION
ORDER BY AMOUNT DESC
END

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Spica66
Starting Member

23 Posts

Posted - 2012-08-06 : 11:32:18

Wow!! Thank you for that quick response. I was just coming back to add that I was also hoping to get the count of the rows that made up the group, and you had already posted. Any thoughts on getting the counts as well?

Spica66
Starting Member

23 Posts

Posted - 2012-08-06 : 11:59:53

I am afraid that your proposed code did not work. There are $28,000 worth of Paint Repairs, but your query returned $344,000.

The amount calculation is as follows:

((SELECT ROUND(ISNULL(SUM(PRICE*QTY),0),2)
FROM NFWARRANTYCLAIMSITEMS i
WHERE i.CLAIMID = w.CLAIMID AND i.CORRECTIONCODE = w.CORRECTIONCODE)+
(SELECT ROUND(ISNULL(SUM(AMOUNT),0),2)
FROM NFWARRANTYMISCCOSTS m
WHERE m.CLAIMID = w.CLAIMID AND m.CORRECTIONCODE = w.CORRECTIONCODE))
+ (W.HOURS*W.PRICE) as AMOUNT

Some of that code disappeared from your response.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 12:24:10

quote:
Originally posted by Spica66

I am afraid that your proposed code did not work. There are $28,000 worth of Paint Repairs, but your query returned $344,000.

The amount calculation is as follows:

((SELECT ROUND(ISNULL(SUM(PRICE*QTY),0),2)
FROM NFWARRANTYCLAIMSITEMS i
WHERE i.CLAIMID = w.CLAIMID AND i.CORRECTIONCODE = w.CORRECTIONCODE)+
(SELECT ROUND(ISNULL(SUM(AMOUNT),0),2)
FROM NFWARRANTYMISCCOSTS m
WHERE m.CLAIMID = w.CLAIMID AND m.CORRECTIONCODE = w.CORRECTIONCODE))
+ (W.HOURS*W.PRICE) as AMOUNT

Some of that code disappeared from your response.

nope
i've replaced subqueries with joins. see the full suggestion and last two joins

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 12:25:17

quote:
Originally posted by Spica66

I am afraid that your proposed code did not work. There are $28,000 worth of Paint Repairs, but your query returned $344,000.

The amount calculation is as follows:

((SELECT ROUND(ISNULL(SUM(PRICE*QTY),0),2)
FROM NFWARRANTYCLAIMSITEMS i
WHERE i.CLAIMID = w.CLAIMID AND i.CORRECTIONCODE = w.CORRECTIONCODE)+
(SELECT ROUND(ISNULL(SUM(AMOUNT),0),2)
FROM NFWARRANTYMISCCOSTS m
WHERE m.CLAIMID = w.CLAIMID AND m.CORRECTIONCODE = w.CORRECTIONCODE))
+ (W.HOURS*W.PRICE) as AMOUNT

Some of that code disappeared from your response.

also reg your total issue i cant suggest anything unless i see how data is. so if you can post the data from tables then i may be able to help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources