this is my query
select distinct tb.ES_TITLE,tb.ES_AUTHOR,tb.ES_SUBJECT,tb.ES_EDITION
,sum(tb.ES_TOTAL_ITEMS)as ES_TOTAL_ITEMS,sum(tb.ES_AVAILABLE)as ES_AVAILABLE
,sum(tb.ES_OUT) as ES_OUT,tb.ES_MISSING,tb.ES_ACCESS_NO
from (select ES_TITLE,ES_AUTHOR,ES_SUBJECT,ES_EDITION,COUNT(*) as ES_TOTAL_ITEMS,
isnull(case when ES_STATUS='Active' then count(*) end,0) as ES_AVAILABLE,
isnull(case when ES_STATUS='Issued' then count(*) end,0) as ES_OUT,'0' as ES_MISSING,
'' as ES_ACCESS_NO from es_library_books b
inner join ES_DEPARTMENTS d on d.ES_ID=b.ES_DEPT_ID
left join ES_BRANCHES on ES_BRANCHES.Es_Id=b.ES_BRANCH
where b.ES_BRANCH=1 and ES_BRANCHES.ES_INSTITUTE_ID= 1
group by ES_TITLE,ES_AUTHOR,ES_SUBJECT,ES_EDITION,ES_STATUS
having b.ES_AUTHOR like '%Gupta J.B%')as tb
group by tb.ES_TITLE,tb.ES_AUTHOR,tb.ES_SUBJECT,tb.ES_EDITION
,tb.ES_TOTAL_ITEMS,tb.ES_AVAILABLE
,tb.ES_OUT,tb.ES_MISSING,tb.ES_ACCESS_NO
this is my result
title | author | edition | total | available | out
-----------------------------------------------------------------------------
A Course In Electrical Power | Gupta J.B | 14 | 1 | 0 | 1
A Course In Electrical Power | Gupta J.B | 14 | 9 | 9 | 0
Electrical Technology | Gupta J.B | 2 | 5 | 5 | 0
Energy Conversion | Gupta J.B | | 9 | 9 | 0
Theory & Performance | Gupta J.B | 14 | 4 | 0 | 4
Theory & Performance | Gupta J.B | 14 | 6 | 6 | 0
but i want like this result
title | author | edition| total | available | out
-----------------------------------------------------------------------------
A Course In Electrical Power| Gupta J.B | 14 | 10 | 9 | 1
Electrical Technology | Gupta J.B | 2 | 5 | 5 | 0
Energy Conversion Gupta J.B | | 9 | 9 | 0
Theory & Performance Gupta J.B | 14 | 10 | 6 | 4
pls help me..