Count Group By 10

• texpic

  SSCertifiable

  Points: 5885

  More actions

  August 18, 2012 at 6:15 am

  #392980

  I'm trying to create a report that groups by 10. Desired result is range and count.

  Thanks

  0 = 3

  10 = 4

  20 = 2

  30 = 3

  CREATE TABLE #Range

  (Amt money)

  INSERT INTO #Range (Amt)

  SELECT 0 UNION ALL

  SELECT 0 UNION ALL

  SELECT 0 UNION ALL

  SELECT 1.01 UNION ALL

  SELECT 3.01 UNION ALL

  SELECT 7.01 UNION ALL

  SELECT 7.01 UNION ALL

  SELECT 11.01 UNION ALL

  SELECT 13.01 UNION ALL

  SELECT 21.01 UNION ALL

  SELECT 23.01 UNION ALL

  SELECT 27.01

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  August 18, 2012 at 6:30 am

  #1527321

  Not sure what your expected output is, but the function you want is "ceiling".

  SELECT Amt, CEILING(Amt/10)*10 AS groupByTens

  FROM #Range;

  Returns: -

  Amt groupByTens

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

  0.00 0.00

  0.00 0.00

  0.00 0.00

  1.01 10.00

  3.01 10.00

  7.01 10.00

  7.01 10.00

  11.01 20.00

  13.01 20.00

  21.01 30.00

  23.01 30.00

  27.01 30.00

  If you want it as counts, then try this: -

  SELECT CEILING(Amt/10)*10 AS groupByTens, COUNT(1) AS counter

  FROM #Range

  GROUP BY CEILING(Amt/10)*10;

  Returns: -

  groupByTens counter

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

  0.00 3

  10.00 4

  20.00 2

  30.00 3

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• texpic

  SSCertifiable

  Points: 5885

  More actions

  August 18, 2012 at 6:41 am

  #1527323

  perfect, thanks