InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 00:45:11 GMT20http://www.sqlservercentral.com/Forums/Topic410817-169-1.aspxthanks a lot its workTue, 16 Oct 2007 00:06:26 GMTbsasijuhttp://www.sqlservercentral.com/Forums/Topic410817-169-1.aspxSounds like you might be looking for a "pivot query" solution. Something like:Select M.BookId, M.booktitle, sum(case when d.status=1 then 1 else 0 end) as TotalIn, sum(case when d.status=2 then 1 else 0 end) as TotalOut, sum(case when d.status=3 then 1 else 0 end) as TotalLost, sum(case when d.status=4 then 1 else 0 end) as TotalDamaged, sum(case when d.status=5 then 1 else 0 end) as TotalRemoved from [master Book table] Minner join [details Book table] Don M.bookID = D.BookIdGroup by M.BookId, M.booktitleMon, 15 Oct 2007 09:03:56 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic410817-169-1.aspxthank for you answer but I hope you did not get me well I want to get sum of status Book i.e In book,out Book Lost book,Damage and Removed as table fields for each title bookthanks in advanceMon, 15 Oct 2007 08:07:44 GMTbsasijuhttp://www.sqlservercentral.com/Forums/Topic410817-169-1.aspxMaybe this gets you started...[i]Select M.BookId, D.[status], count(*) as NumberOfOccurrencesfrom [master Book table] Minner join [details Book table] Don M.bookID = D.BookIdGroup by M.BookId, D.Status[/i]Mon, 15 Oct 2007 07:49:02 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic410817-169-1.aspxHi all have developed my library project counter problem when I want to get sum book status (in , out ,lost,damage and removed) of the same title from master Book table vs details Book table in one row.Master book table link with detail table field bookID and their status field on the book details have 1=in,2=out,3=lost,4=damage,5=removed .I tried to use group by method but got sum their status of same title different rowsPlease need you helpthank in advancedMon, 15 Oct 2007 07:39:28 GMTbsasiju