Reply to thread

Message: <blockquote data-quote="0cean" data-source="post: 22634499" data-attributes="member: 562898">Query eka nam therune naha. Habayi wade nam hari. <img src="/styles/default/xenforo/smilies/default/yes.gif"  class="smilie" loading="lazy" alt=":yes:" title="Yes    :yes:" data-shortname=":yes:" />Thanks machan. <img src="/styles/default/xenforo/smilies/default/happy.gif"  class="smilie" loading="lazy" alt=":)" title="Happy    :)" data-shortname=":)" />[CODE]declare @mytable table(id int, stname char(10), birthday date);insert into @mytable values (1, 'saman', '2015.01.02')insert into @mytable values (2, 'nimal', '2016.02.02')insert into @mytable values (3, 'amal', '2017.05.02')insert into @mytable values (4, 'kamal', '2015.09.02')insert into @mytable values (5, 'nayana', '2015.08.02')select x.*, coalesce( y.count,0) from (SELECT distinct year(birthday) year,a.*FROM @mytable ,( select 1 'month'unionselect 2 'month'unionselect 3 'month'unionselect 4 'month'unionselect 5 'month'unionselect 6 'month'unionselect 7 'month'unionselect 8 'month'unionselect 9 'month'unionselect 10 'month'unionselect 11 'month'unionselect 12 'month') a) x left outer join (SELECT year(birthday) year , month (birthday) month ,count(*) count FROM @mytablegroup by year(birthday) ,month (birthday)) yon x.year=y.year and x.month=y.month[B]order by x.year, x.month[/B][/CODE]</blockquote>

Top Bottom