SQLTeam.com | Weblogs | Forums

Grouping by 4 minutes

greetings,

given the following sample data, how would I go about grouping and summing these in groups of 4 minutes? thanks!

declare @gohawks table(mnt int, cnt int, meta varchar(50) )

insert into @gohawks
select 0,17424 as cnt, 'alum' union 
select 0,3800 as cnt, 'brnz' union 
select 0,1020 as cnt, 'gld' union 
select 0,806 as cnt, 'zyt' union 
select 0,663 as cnt, 'bumin' union 
select 0,271 as cnt, 'qrtz' union 
select 0,218 as cnt, 'copper' union 
select 0,166 as cnt, 'irn' union 
select 0,103 as cnt, 'tit' union 
select 0,8 as cnt, 'act' union 
select 1,17853 as cnt, 'alum' union 
select 1,3813 as cnt, 'brnz' union 
select 1,803 as cnt, 'zyt' union 
select 1,729 as cnt, 'bumin' union 
select 1,662 as cnt, 'gld' union 
select 1,226 as cnt, 'copper' union 
select 1,108 as cnt, 'irn' union 
select 1,89 as cnt, 'tit' union 
select 1,42 as cnt, 'qrtz' union 
select 1,8 as cnt, 'act' union 
select 2,18408 as cnt, 'alum' union 
select 2,3793 as cnt, 'brnz' union 
select 2,796 as cnt, 'zyt' union 
select 2,785 as cnt, 'bumin' union 
select 2,652 as cnt, 'gld' union 
select 2,216 as cnt, 'copper' union 
select 2,129 as cnt, 'qrtz' union 
select 2,123 as cnt, 'tit' union 
select 2,83 as cnt, 'irn' union 
select 2,2 as cnt, 'act' union 
select 3,18517 as cnt, 'alum' union 
select 3,3866 as cnt, 'brnz' union 
select 3,856 as cnt, 'bumin' union 
select 3,798 as cnt, 'zyt' union 
select 3,718 as cnt, 'gld' union 
select 3,246 as cnt, 'copper' union 
select 3,120 as cnt, 'irn' union 
select 3,111 as cnt, 'tit' union 
select 3,83 as cnt, 'qrtz' union 
select 3,7 as cnt, 'act' union 
select 4,18440 as cnt, 'alum' union 
select 4,3776 as cnt, 'brnz' union 
select 4,788 as cnt, 'zyt' union 
select 4,758 as cnt, 'bumin' union 
select 4,648 as cnt, 'gld' union 
select 4,234 as cnt, 'copper' union 
select 4,119 as cnt, 'tit' union 
select 4,91 as cnt, 'irn' union 
select 4,58 as cnt, 'qrtz' union 
select 4,15 as cnt, 'act' union 
select 5,18372 as cnt, 'alum' union 
select 5,3729 as cnt, 'brnz' union 
select 5,855 as cnt, 'bumin' union 
select 5,783 as cnt, 'zyt' union 
select 5,653 as cnt, 'gld' union 
select 5,254 as cnt, 'copper' union 
select 5,109 as cnt, 'tit' union 
select 5,97 as cnt, 'irn' union 
select 5,67 as cnt, 'qrtz' union 
select 5,6 as cnt, 'act' union 
select 6,17559 as cnt, 'alum' union 
select 6,3658 as cnt, 'brnz' union 
select 6,811 as cnt, 'bumin' union 
select 6,807 as cnt, 'zyt' union 
select 6,595 as cnt, 'gld' union 
select 6,237 as cnt, 'copper' union 
select 6,100 as cnt, 'tit' union 
select 6,95 as cnt, 'irn' union 
select 6,67 as cnt, 'qrtz' union 
select 6,2 as cnt, 'act' union 
select 7,17793 as cnt, 'alum' union 
select 7,3858 as cnt, 'brnz' union 
select 7,926 as cnt, 'bumin' union 
select 7,800 as cnt, 'zyt' union 
select 7,692 as cnt, 'gld' union 
select 7,228 as cnt, 'copper' union 
select 7,107 as cnt, 'irn' union 
select 7,94 as cnt, 'tit' union 
select 7,72 as cnt, 'qrtz' union 
select 7,5 as cnt, 'act' union 
select 8,17401 as cnt, 'alum' union 
select 8,3701 as cnt, 'brnz' union 
select 8,807 as cnt, 'bumin' union 
select 8,778 as cnt, 'zyt' union 
select 8,646 as cnt, 'gld' union 
select 8,229 as cnt, 'copper' union 
select 8,88 as cnt, 'irn' union 
select 8,72 as cnt, 'tit' union 
select 8,72 as cnt, 'qrtz' union 
select 8,2 as cnt, 'act' union 
select 9,18204 as cnt, 'alum' union 
select 9,3795 as cnt, 'brnz' union 
select 9,866 as cnt, 'bumin' union 
select 9,827 as cnt, 'zyt' union 
select 9,652 as cnt, 'gld' union 
select 9,247 as cnt, 'copper' union 
select 9,104 as cnt, 'tit' union 
select 9,98 as cnt, 'irn' union 
select 9,72 as cnt, 'qrtz' union 
select 9,3 as cnt, 'act' union 
select 10,17560 as cnt, 'alum' union 
select 10,3781 as cnt, 'brnz' union 
select 10,856 as cnt, 'bumin' union 
select 10,754 as cnt, 'zyt' union 
select 10,655 as cnt, 'gld' union 
select 10,242 as cnt, 'copper' union 
select 10,102 as cnt, 'tit' union 
select 10,95 as cnt, 'irn' union 
select 10,92 as cnt, 'qrtz' union 
select 10,6 as cnt, 'act' union 
select 11,18224 as cnt, 'alum' union 
select 11,3984 as cnt, 'brnz' union 
select 11,880 as cnt, 'bumin' union 
select 11,806 as cnt, 'zyt' union 
select 11,705 as cnt, 'gld' union 
select 11,245 as cnt, 'copper' union 
select 11,117 as cnt, 'tit' union 
select 11,97 as cnt, 'irn' union 
select 11,71 as cnt, 'qrtz' union 
select 11,2 as cnt, 'act' union 
select 12,17916 as cnt, 'alum' union 
select 12,3938 as cnt, 'brnz' union 
select 12,924 as cnt, 'bumin' union 
select 12,834 as cnt, 'zyt' union 
select 12,650 as cnt, 'gld' union 
select 12,208 as cnt, 'copper' union 
select 12,106 as cnt, 'tit' union 
select 12,104 as cnt, 'irn' union 
select 12,66 as cnt, 'qrtz' union 
select 12,9 as cnt, 'act' union 
select 13,18009 as cnt, 'alum' union 
select 13,3851 as cnt, 'brnz' union 
select 13,825 as cnt, 'zyt' union 
select 13,776 as cnt, 'bumin' union 
select 13,659 as cnt, 'gld' union 
select 13,223 as cnt, 'copper' union 
select 13,87 as cnt, 'irn' union 
select 13,87 as cnt, 'tit' union 
select 13,50 as cnt, 'qrtz' union 
select 13,2 as cnt, 'act' union 
select 14,18130 as cnt, 'alum' union 
select 14,3875 as cnt, 'brnz' union 
select 14,827 as cnt, 'bumin' union 
select 14,793 as cnt, 'zyt' union 
select 14,672 as cnt, 'gld' union 
select 14,207 as cnt, 'copper' union 
select 14,113 as cnt, 'irn' union 
select 14,102 as cnt, 'tit' union 
select 14,55 as cnt, 'qrtz' union 
select 14,8 as cnt, 'act' union 
select 15,17564 as cnt, 'alum' union 
select 15,3739 as cnt, 'brnz' union 
select 15,803 as cnt, 'bumin' union 
select 15,776 as cnt, 'zyt' union 
select 15,598 as cnt, 'gld' union 
select 15,237 as cnt, 'copper' union 
select 15,117 as cnt, 'irn' union 
select 15,105 as cnt, 'tit' union 
select 15,92 as cnt, 'qrtz' union 
select 15,6 as cnt, 'act' union 
select 16,17909 as cnt, 'alum' union 
select 16,3963 as cnt, 'brnz' union 
select 16,805 as cnt, 'bumin' union 
select 16,797 as cnt, 'zyt' union 
select 16,622 as cnt, 'gld' union 
select 16,232 as cnt, 'copper' union 
select 16,164 as cnt, 'tit' union 
select 16,111 as cnt, 'irn' union 
select 16,90 as cnt, 'qrtz' union 
select 16,1 as cnt, 'act' union 
select 17,17807 as cnt, 'alum' union 
select 17,3927 as cnt, 'brnz' union 
select 17,1048 as cnt, 'bumin' union 
select 17,837 as cnt, 'zyt' union 
select 17,676 as cnt, 'gld' union 
select 17,236 as cnt, 'copper' union 
select 17,119 as cnt, 'irn' union 
select 17,105 as cnt, 'tit' union 
select 17,78 as cnt, 'qrtz' union 
select 17,6 as cnt, 'act' union 
select 18,16278 as cnt, 'alum' union 
select 18,4004 as cnt, 'brnz' union 
select 18,1094 as cnt, 'bumin' union 
select 18,831 as cnt, 'zyt' union 
select 18,649 as cnt, 'gld' union 
select 18,245 as cnt, 'copper' union 
select 18,117 as cnt, 'irn' union 
select 18,89 as cnt, 'tit' union 
select 18,70 as cnt, 'qrtz' union 
select 18,7 as cnt, 'act'  

I am thinking of using either NTILE or dense_rank, both of which I have never used.

hi yosiasz

NTILE will do it automatically for you

thanks @harishgg1 but I dont want it to do it automatically for me. I want it to group by 4 minutes

SELECT mnt/4 * 4 AS mnt_from
	,(mnt/4 * 4) + 3 AS mnt_to
	,SUM(cnt) AS cnt
FROM #gohawks
GROUP BY mnt/4
3 Likes

We can crank @Ifor 's good code up a notch. Check out the sub-totals an grand-total. Based on the give data, I'm not sure that they're all appropriate here but the bad ones can easily be filtered out. I'm just showing "possibilities" here..And, like @Ifor did, I used a Temp Table instead of a (ugh!) Table Variable to make things a little easier to play with.

 SELECT  mnt_from = mnt/4 * 4
	    ,mnt_to   = mnt/4 * 4 + 3
        ,meta
	    ,cnt      = SUM(cnt)
        ,RowType  = CASE GROUPING(mnt/4)*10+GROUPING(meta)
                    WHEN  0 THEN 'Detail'
                    WHEN 10 THEN 'Sub-Total Meta'
                    WHEN  1 THEN 'Sub-Total Minute4'
                    WHEN 11 THEN 'Grand-Total'
                    END
   FROM #gohawks
  GROUP BY mnt/4, meta WITH CUBE
  ORDER BY GROUPING(mnt/4),mnt/4,GROUPING(meta),meta
;
1 Like

Thanks very much @Ifor !

great stuff as usual @JeffModen ! Now, I need to implement this for a 24 hour period and break it down by 4 minutes buckets starting at midnight, this has given me a great lead! thanks all