Click here to Skip to main content
15,881,938 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
my query is

SQL
;with mycte as
(select (select sum(b.ES_COST) 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 ) as Total,
b.ES_ID,b.ES_TITLE,b.ES_BOOK_TYPE,b.ES_BOOK_REMARKS,b.ES_DEPT_ID, row_number() over(partition by b.es_title, b.ES_AUTHOR,b.ES_EDITION order by b.es_title) rn,
b.ES_CLASS_DETAIL_ID,b.ES_ENTERED_BY,b.ES_SPECIALIZATION,b.ES_SUBJECT as SUBJECT,
b.ES_ENTRY_DATE,b.ES_YEAR,d.ES_DEPT_NAME,b.ES_EDITION,b.ES_AUTHOR,b.ES_PUBLISHER,b.ES_COST,b.ES_VOLUME,b.ES_PAGES,b.ES_SOURCE,b.ES_BILL_NO,b.ES_BILL_DATE ,b.ES_BAR_CODE,b.ES_BRANCH,b.ES_CALL_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 )
SELECT CASE WHEN rn > 1 THEN '' ELSE  ES_AUTHOR  END AS  ES_AUTHOR,es_title,

       CASE WHEN rn > 1 THEN '' ELSE  ES_EDITION END AS  ES_EDITION,
       case when rn> 1 then  '' else  ES_PAGES  end  as es_pages

FROM   mycte



i want to count the books and set into one columns
Posted
Comments
CHill60 30-Jan-15 7:01am    
What's wrong with using COUNT(b.ES_ID) in your select?
And that is one heck of a join - I suspect you can simplify your query somewhat
ZurdoDev 30-Jan-15 7:48am    
1. Please format your code some so it is legible.
2. What exactly is your question? You use SUM so I assume you also know about COUNT() unless you just copied this from somewhere.
PhilLenoir 30-Jan-15 12:46pm    
Using count with a field name excludes records where that field is null. this is great if it's the behaviour you want. Using COUNT(*) returns a count including records with null values, but it is slow on large data sets. IMO the best practice for count is COUNT(1).

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900