Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

Execute count(*) on a group-by result-set

By: a guest on Feb 26th, 2012  |  syntax: None  |  size: 1.17 KB  |  views: 19  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. CREATE TABLE  `sandbox`.`orders` (
  2.   `year` int,
  3.   `month` int,
  4.   `day` int,
  5.   `desc` varchar(255)
  6. )
  7.  
  8. INSERT INTO orders (year, month, day, desc)  
  9. VALUES (2009,1,1, 'New Years Resolution 1')
  10.       ,(2009,1,1, 'Promise lose weight')
  11.       ,(2009,1,2, 'Bagel')
  12.       ,(2009,1,12, 'Coffee to go')
  13.        
  14. SELECT [Year], [Month], [Day]
  15. INTO #Some_Days
  16. FROM Quarter
  17. WHERE Start >= '2009-01-01' AND [End] < '2009-01-16'
  18. GROUP BY [Year], [Month], [Day]
  19.  
  20. SELECT count(*) from #Some_Days
  21.        
  22. SELECT COUNT(*) FROM
  23.     (SELECT DISTINCT(SomeColumn)
  24.        FROM MyTable
  25.       WHERE Something BETWEEN 100 AND 500
  26.       GROUP BY SomeColumn) MyTable
  27.        
  28. DATEADD(YEAR, [Quarter].Year, DATEADD(MONTH, [Quarter].Month, DATEADD(DAY, [Quarter].DAY, 0), 0), 0)
  29.        
  30. SELECT
  31.     COUNT(DISTINCT DATEADD(YEAR, [Quarter].Year, DATEADD(MONTH, [Quarter].Month, DATEADD(DAY, [Quarter].DAY, 0), 0), 0))
  32. FROM
  33.     Quarter
  34. WHERE
  35.     [Quarter].Start >= '2009-01-01'
  36.     AND [Quarter].End < '2009-01-16'
  37.        
  38. SELECT COUNT(DISTINCT day) FROM orders
  39. WHERE (year, month) = (2009, 1);
  40.        
  41. SELECT [Year], [Month], [Day]
  42. FROM Quarter
  43. WHERE Start >= '2009-01-01' AND [End] < '2009-01-16'
  44. GROUP BY [Year], [Month], [Day]
  45. COMPUTE COUNT(*)