Getting Top 3 Results - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Getting Top 3 Results

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-12 : 13:45:01

Arvin writes "Hi guys, i'm about to give up on this, see i have this stored procedure.

/*------------------------------------------------------------

@pltcd VARCHAR(30), @tagno VARCHAR(30)

AS

SELECT PHENAME, SUM(PHECNT) AS PHESUM
FROM (SELECT TFP1010.PHENM AS PHENAME, COUNT(*) AS PHECNT
      FROM TFP1010 JOIN
      TFP5030 ON TFP1010.PHECD = TFP5030.PHECD1
      WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
      GROUP BY TFP1010.PHENM
      UNION ALL
      SELECT TFP1010.PHENM, COUNT(*)
      FROM TFP1010 JOIN
      TFP5030 ON TFP1010.PHECD = TFP5030.PHECD2
      WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
      GROUP BY TFP1010.PHENM
      UNION ALL
      SELECT TFP1010.PHENM, COUNT(*)
      FROM TFP1010 JOIN
      TFP5030 ON TFP1010.PHECD = TFP5030.PHECD3
      WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
      GROUP BY TFP1010.PHENM
      UNION ALL
      SELECT TFP1010.PHENM, COUNT(*)
      FROM TFP1010 JOIN
      TFP5030 ON TFP1010.PHECD = TFP5030.PHECD4
      WHERE TFP5030.PLTCD = @pltcd AND TFP5030.TAGNO = @tagno
      GROUP BY TFP1010.PHENM) 
AS PHETOP
GROUP BY PHENAME
ORDER BY PHESUM DESC

/*------------------------------------------------------------

the output of that stored procedure is this.

/*------------------------------------------------------------
_______________________
PHENAME		 PHESUM
_______________________
OUT OF CLEARANCE  3
ELONGATION	  2
WRONG CLEARANCE	  2
A LINING	  1
CORROSION	  1
DAMAGE		  1
FLUSHING	  1
OTHERS		  1
UNBALANCE	  1

/*------------------------------------------------------------

but what i need is display the items with values of 1 as OTHERS. my purpose for this is to plot a pie chart to display the top 3 items (OUT OF CLEARANCE,ELONGATION, AND WRONG CLEARANCE) having values more than 1 and items withvalues of 1 should be considered and displayed as OTHERS.

hope you could help on this guys, i need it badly.

thanks in advance,

arvin"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources