3

My head is smoking from (stupid) tries of using JOIN, WITH and GROUP BY to come up with a solution for my pretty common scenario - I just can't wrap my head around it. Let me throw the example at you right away:

I have two tables (ColorCount and Colorname):

ColorCount:
ColorID Count Date
1       42    2010-09-07
1       1     2010-09-08
2       22    2010-09-14
1       20    2010-10-10
3       4     2010-10-14

ColorName:
ColorID  Name
1        Purple
2        Green
3        Yellow
4        Red

Now all I want is to join the ColorName table to the ColorCount table, sum up all the counts of colors per month and calculate the percentage of each count from the the monthly total. Tables are better than words:

Output:
Month Color   Count Percentage
09    Purple  43    66%
09    Green   22    33%
09    Yellow  0     0%
09    Red     0     0%
10    Purple  20    83%
10    Green   0     0%
10    Yellow  4     16%
10    Red     0     0%

(Please note the total Count of Month 09 is 65, hence the 66% for Purple and also the 0's for non-existing colors):

I hope somebody dreams in SQL and this is an easy task...

Joe Doyle
  • 6,363
  • 3
  • 42
  • 45
Dennis G
  • 21,405
  • 19
  • 96
  • 133

2 Answers2

3

This works, with the following caveats:

  • The datetime values must be date only
  • It only lists those months for which there is any data
  • I list by first day of the month, in case you have data that crosses years (I'm assuming you don't want to aggregate data from Jan 2009 with data from Jan 2010)
  • The precise Percentage column formatting details I leave up to you, I gotta get back to work

Code:

;with cte (ColorId, Mth, TotalCount)
 as (select
        ColorId
       ,dateadd(dd, -datepart(dd, Date) + 1, Date) Mth
       ,sum(Count) TotalCount
      from ColorCount
      group by ColorId, dateadd(dd, -datepart(dd, Date) + 1, Date))
 select
    AllMonths.Mth [Month]
   ,cn.Name
   ,isnull(AggData.TotalCount, 0) [Count]
   ,isnull(100 * AggData.TotalCount / sum(AggData.TotalCount * 1.00) over (partition by AllMonths.Mth), 0) Percentage
  from (select distinct Mth from cte) AllMonths
   cross join ColorName cn
   left outer join cte AggData
    on AggData.ColorId = cn.ColorId
     and AggData.Mth = AllMonths.Mth
  order by AllMonths.Mth, cn.ColorId
Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • Good discussion of windowing techniques on SimpleTalk, at http://www.simple-talk.com/sql/learn-sql-server/working-with-window-functions-in-sql-server/?utm_source=simpletalk&utm_medium=email-main&utm_content=WindowsFunction-20101101&utm_campaign=SQL – Philip Kelley Nov 16 '10 at 18:42
2
SELECT
    [Month],
    [Name],
    [Count],
    CASE WHEN TotalMonth=0 THEN 'INF' ELSE cast(round([Count],0)*100.0/TotalMonth,0) as int) + '%' END as [Percentage]
FROM 
(
SELECT 
    [Months].[Month] as [Month],
    CN.[Name],
    isnull(CC.[Count],0) as [Count],
    (SELECT SUM([Count]) FROM ColorCount WHERE 
            datepart(month,[Date])=datepart(month,CC.[Date])
     ) as [TotalMonth]
FROM (SELECT DISTINCT datepart(month,[Date]) as [Month] FROM ColorCount) [Months]
LEFT JOIN ColorName CN ON [Months].[Month]=datepart(month,CC.[Date])
LEFT JOIN ColorCount CC ON CN.ColorID=CC.ColorID
) AS tbl1
ORDER BY
    [Month] ASC,
    [Name] ASC

Something like that.... It won't display the leading zero of the month, but does it really matter?

Alex
  • 14,338
  • 5
  • 41
  • 59