Archives of the TeradataForum

Message Posted: Fri, 04 Feb 2011 @ 16:47:01 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

It may or not be faster than count distinct, but you can use an ordered analytic function to find the "first" row with a new C1 + C4 combination and use that to generate the equivalent of a distinct count.

This uses sys_calendar.calendar fields in place of your C1...C4 (month_of_year is C4):

     select day_of_week, sum(day_of_calendar), count(day_of_year),
     sum(is_first)
     from
     (select day_of_week, day_of_calendar, day_of_year, case when row_number() over
     (partition by day_of_week, month_of_year order by 1) = 1 then 1 else 0 end
     is_first from sys_calendar.calendar where day_of_calendar < 500) x group by 1;

I couldn't get the other suggestion (sel c1,sum(c2),count(c3),count(c4) over(partition by c1 order by 1) from vt1 group by c1,c4;) to work as is, but you could wrap it to get the same output:

     select day_of_week, sum(doc), sum(doy), min(moy)
     from
     (sel day_of_week,sum(day_of_calendar) doc,count(day_of_year) doy,
         count(month_of_year) over(partition by day_of_week) moy
         from sys_calendar.calendar
         where  day_of_calendar < 500
         group by day_of_week,month_of_year) x
     group by 1;

The second one might be faster since it doesn't require any ordering (I removed the order by in the partition since it doesn't change the count).

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>