Grouping

• Mark Cowne

  One Orange Chip

  Points: 26986

  More actions

  March 27, 2009 at 3:34 am

  #967258

  May be this?

  with cte(productname,turnover) as (

  Select case when row_number() over (order by sum(turnover) desc) >= 5 then 'Other products' else productname end,

  sum(turnover)

  from sales

  group by productname)

  select productname,sum(turnover)

  from cte

  group by productname

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  March 27, 2009 at 3:57 am

  #967266

  Mark beat me to it - but here's a slightly different way:

  -- make some sample data

  DROP TABLE #sales

  CREATE TABLE #sales (productname VARCHAR(20), turnover MONEY)

  INSERT INTO #sales (productname, turnover)

  SELECT 'redwine', 40 UNION ALL

  SELECT 'Whitewine', 20 UNION ALL

  SELECT 'beers', 10 UNION ALL

  SELECT 'Whisky', 10 UNION ALL

  SELECT 'Non-alcohol', 8 UNION ALL

  SELECT 'Vodka', 5 UNION ALL

  SELECT 'Rum', 4 UNION ALL

  SELECT 'RTD', 3

  -- Run query:

  ;WITH myCTE(productname, SUMturnover, t_order) AS

  (SELECT productname, SUM(turnover), ROW_NUMBER() OVER (ORDER BY SUM(turnover) DESC)

  FROM #sales

  GROUP BY productname)

  SELECT productname, SUMturnover

  FROM myCTE

  WHERE t_order <= 5

  UNION ALL

  SELECT productname = 'All others', SUM(SUMturnover)

  FROM myCTE

  WHERE t_order > 5

  Results

  productname SUMturnover

  -------------------- ---------------------

  redwine 40.00

  Whitewine 20.00

  Whisky 10.00

  beers 10.00

  Non-alcohol 8.00

  All others 12.00

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden