Grouping

• Søren

  SSChasing Mays

  Points: 628

  More actions

  March 27, 2009 at 3:04 am

  #128905

  Hi have a solution, where I want to show a pie-chart with a customers purcases per productgroup. The problem is that sometimes there are purchases in 2-3 productgroups and other times there are purchases in 50 productgroups.

  A piechart is not looking well, with 50 productgroups, so O need to make an SQL-statement that alway shows 5 or less productgroups, and if more than 5 then productgroup5 is a summarization of the rest. An example:

  Customer A has purchased the following productgroups:

  redwine 40%

  Whitewine 20%

  beers 10%

  Whisky 10%

  Non-alcohol 8%

  Vodka 5%

  Rum 4%

  RTD 3%

  To retrieve this i have this statement:

  Select productname, sum(turnover)

  from sales

  group by productname

  PieChart changes the turnover to percentage.

  But what I want is to show the following in the piechart:

  Redwine 40%

  whitewin 20%

  Beers 10%

  Whisky 10%

  Other products 20%

  HOW DO I DO THAT?? ANY LINKS OR OTHER RESSOURCES?

  thanks very much.

  Søren

  Søren,
  MCITP SQL 2008 (administration / programming), MCITP SQL 2005 (BI), MCT

• Mark Cowne

  One Orange Chip

  Points: 26984

  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