Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    9

    Unanswered: SUM Top 10 Results

    Morning All,

    I am trying to sum the top 10 results from a table within and existing query. As per below.

    Code:
    SELECT     dbo.Items.DESCR AS DESCRIPTION, SUM(Tran1.QTY) AS USE90DAYS, GETDATE() - 90 AS PERIOD, SUM(CAST(Tran1.QTY AS dec(18, 2))) 
                          * 4 / 90 AS AVG_3DAYUSE, dbo.Items.MAXQTY
    FROM         dbo.TransactionLog AS Tran1 LEFT OUTER JOIN
                          dbo.Items ON Tran1.ITEMNUMBER = dbo.Items.ITEMNUMBER
    WHERE     (Tran1.TRANSCODE LIKE N'WN') AND (Tran1.TRANENDDATETIME > GETDATE() - 90)
    GROUP BY dbo.Items.DESCR, dbo.Items.MAXQTY
    I have this statement that works fine but i now need to find the top 10 QTY and total them within the existing table.

    Can some one point me in the rigth direct, let me know if you require any more information for a resolution.

    Sam

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    WITH CTE AS
    (SELECT ITEMNUMBER,
    	QTY, 
    	ROW_NUMBER() OVER (PARTITION BY ITEMNUMBER ORDER BY QTY DESC) as RowNum
    FROM dbo.TransactionLog 
    WHERE TRANSCODE LIKE N'WN' AND 
    	TRANENDDATETIME > GETDATE() - 90
    )
    SELECT dbo.Items.DESCR AS DESCRIPTION, 
    	SUM(Tran1.QTY) AS USE90DAYS, 
    	GETDATE() - 90 AS PERIOD, 
    	SUM(CAST(Tran1.QTY AS dec(18, 2))) * 4 / 90 AS AVG_3DAYUSE, 
    	dbo.Items.MAXQTY
    FROM dbo.Items
    	LEFT OUTER JOIN CTE AS Tran1 ON 
    		dbo.Items.ITEMNUMBER = Tran1.ITEMNUMBER AND
    		Tran1.RowNum <= 10
    GROUP BY dbo.Items.DESCR, dbo.Items.MAXQTY
    I reversed the tables of the LEFT OUTER JOIN. Having a GROUP BY defined on columns that originate from the optional side of an OUTER JOIN is something I have never done before. It just feels wrong.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    May 2009
    Posts
    9
    I should of mentioned this in my post, i am running sql expres 05 which i believe doesnt support the over statement?

    Error: The OVER SQL construct or statement is not supported.

  4. #4
    Join Date
    May 2009
    Posts
    9
    Does anyone have any ideas?

    Thanks
    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •