sum of total items ,available items,out item from duplicate records in sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

this is my query

SQL

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..

Posted 7-Jan-15 18:33pm

Member 10776340

Add a Solution

Comments

ArunRajendra 8-Jan-15 0:48am

Do you need tb.ES_MISSING,tb.ES_ACCESS_NO field values?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Answer 1 · 2015-01-07T19:04:00

Try this-

SQL

select tb.ES_TITLE,tb.ES_AUTHOR,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
 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_EDITION

I have just removed the unnecessary fields from the final select list.
Hope, it helps :)