.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Excluding Duplicates in Sum

Posted By:      Posted Date: October 18, 2010    Points: 0   Category :Sql Server

This seems like a dumb question, but how do I exclude duplicates in sum functions?  When I searched for similar questions, all I found were ideas around testing the Previous() row, but I get errors about nested functions when I try to sum those functions.



Corey Furman @ Facebook
Please mark posts as answer or helpful when they are.

View Complete Post

More Related Resource Links

How to union two queries without duplicates


I have a sql query that returns 4 columns
CustName CustId CustZip CustPhone

I have a second sql query that returns the following 5 columns

CustName CustId CustZip CustEmail CustAddress

Both queries, query different data tables in the database, but return columns that are common to

How do I union the two queries(Assuming a union is needed)

Which will result in no duplicates and an end result being the following output:

CustName CustId CustZip CustPhone CustEmail CustAddress


As you can see we want to not have duplicate values on output. So something like the following
is not acceptable:

Jeff Stamper  2222234  81224  498-300-2222
Jeff Stamper  2222234  81224  498-300-2222 js@jj.com 122 Mars Blvd
Karen Bops  3322234    81666  498-300-2222
Karen Bops  3322234    81666  498-300-2222 kb@lpo.com 322 Jamer Road

IEnumerable.Except incorrectly removes duplicates

IEnumerable.Except is expected to receive a second IEnumerable with the elements to be removed from the first. But when the first collection contains duplicates, those are removed too, despite not being present on the second. Check in the following example that 2.3 appears twice on numbers1 but only once on the output. double[] numbers1 = { 2.0, 2.1, 2.2, 2.3, 2.3, 2.4, 2.5 }; double[] numbers2 = { 2.2 }; IEnumerable<double> onlyInFirstSet = numbers1.Except(numbers2); foreach (double number in onlyInFirstSet) Console.WriteLine(number); /* This code produces the following output: 2 2.1 2.3 2.4 2.5 */

Need help in datetime conversion excluding invalid records

I have varachar(50) field which have date in yyyymmdd format Now i have to filter the data which are greater then server date I have following query Select * from MDB_CONTENT_INFO Where convert(datetime, CON_SCHEDULED_INDUCTION_DATE,112) >= GetDate() Problem ist aht i may have some invalid data other then valid one. I am getting error The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. or Conversion failed when converting date and/or time from character string. I wanted to exclude those records but currently getting error for the query asKamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])

remove Duplicates

Hi, I am want to create a primamry key on column a in table FARA, I am receiving an error that I have duplicates in column a. I tried copying the table using : select distinct into table FARA-u from table FARA. thought this should remove duplicates but I am still getting same error when i try to set primamry key on column a. how can i remove those duplicates ( I wil need to remove the whole row that conatins duplicates on column a) Thanks!

Get last seven days records excluding Weekend days ?

Hi , I'm trying to extract records from table inserted in last seven working days . I'm unable to exclude Sunday and Saturday from this : select * from tb_Name where dt >=DateAdd(dd, DateDiff(dd, 0, Getdate()), 0) and dt<DateAdd(dd, DateDiff(dd, 0, GetDate())-7, 0) Any pointers would greatly  be valued and appreciated . Thanks .Rajkumar Yelugu

Need sum only 1 row for hide duplicates rows in a GROUP in Reporting Services

In a report grouped by Collateral Name, I have the same Collateral Amount in multiple rows.  I clicked "hide duplicates" so it only displays once on the report, but it is summing row the Collateral Amount that is sum from a every row that hidden.  Is there a way in RS to only sum the displayed row (or the top row in a group)? As the sample, I had the data as below:  Collateral Name                     Collateral Amount            Outstanding         C-1                                                    100                            20         C-1                                                    100                            15         C-1                                                    100                            30 Now I can show like this with Hide Duplication properties,  Collateral Name                     Collateral Amount            Outstanding         C-1                                                    100                            20                                                                                                    15                                                                                                    30 -----------------------------------------------------------------------------------------                                                                 300                             65 -------------------------

FAST for Sharepoint duplicates components

Setting up FAST for Sharepoint it turns out two unclear functionalities. Both FAST Content and FAST Query SSAs have a own index. We have to define some Content sources on FAST Content, but what about The Content Sources of FAST Query SSA ? They have to be define and crawled or not ? In FAST Query menu we can find "Metadata Properties" link, in FAST Search Administration Page we have "Managed Properties" . These sets do not seem the same. Which one is used by Search Site ? I'm not be able to find anything on this searching online documentation.

SharePoint 2010 Search Crawler excluding items

Hi All I'm trying to setup the Search functionality in SharePoint Server 2010, but I'm having trouble getting it working. I have set up a crawler for the site with a user that has Full Read permissions to everything. I also set up a crawl rule for the site including all items in the path with the "Follow links on the URL without crawl itself" option checked. The crawler runs across the entire Site Collection and sub-sites, but I keep seeing warnings that content was excluded and the search can return no results. The error message I keep seeing is "The content for this address was excluded by the crawler because this item was marked with a no-index meta-tag. To index this item, remove the meta-tag and recrawl." I have looked for a no-index meta-tag, and have been unable to find it anywhere. I have also done searching on this issue, and set site settings to Always index all WebParts and to Appear in Search Results. Neither of those settings appear to have helped.   Does anyone know what I am doing wrong, or know of good documentation that could point me in the right direction? Thanks

Caculated column formula for Workdays between two dates ? Excluding weekends? DateDiff

This gives me the number of days between two days.. great=DATEDIF([Completed],[Issued],"D")is there any way to exclude weekends?

Find Duplicates

Hey Guys,I have the following query:SELECT tmp_random.tempStr FROM tmp_random GROUP BY tmp_random.tempStr HAVING (COUNT(tmp_random.tempStr) > 1) This is working great but I have one nagging issue, for example this returned the string 'uepog' as being duplicated in my table.When doing a search for just this I found these {'uepog', 'uEpog'}Although this is correct as technically they are the same but I want it to only match exact duplicates including case.Can this be done? How ?Many thanks

C# RSS Group Pubdate, remove duplicates

Hello. I am wanting to use c# to grab an RSS feed.  Remove duplicate pubdates and then sort the content by date, displaying the data under the heading of the pudate. I have to say I am at a loss I would be grateful for any suggestions. 

inserting one table data to another after filtering nulls and duplicates

hi i have tbl_ip i want to filter nulls and duplicate to my tbl_ipfilter can u suggest me a stored procedure for this. data of tbl_ip is like below . ip NULL NULL NULL

HashSet of my own object allows duplicates


Hello guys, i have this object:

public class Ramo:IEqualityComparer<Ramo>
    private byte ramo { get; set; }
    private int codigoOperacion { get; set; }
    private int condicionEspecial { get; set; }
    private int tipoCobertura { get; set; }
    public Ramo()

    public Ramo(byte ramo, int codigoOperacion, int tipoCobertura, int condicionEspecial)
        this.ramo = ramo;
        this.codigoOperacion = codigoOperacion;
        this.condicionEspecial = condicionEspecial;
        this.tipoCobertura = tipoCobertura;

    public bool Equals(Ramo r1, Ramo r2)
        if (r1.codigoOperacion == r2.codigoOperacion && r1.condicionEspecial == r2.condicionEspecial &&
            r1.tipoCobertura == r2.tipoCobertura && r1.ramo == r2.ramo)
            return true;
        return false;

    public int GetHashCode(Ramo r1)
        return 0;

I want to create a HashSet of it, so if i use the Add method to insert two objects of Ramo it will detect duplicates:

HashSet<Ramo> ramosDistintos = new HashSet<Ramo>();
Ramo ramo = new Ramo(byte.Parse("32"), int.Parse("1"), int.Parse("0"), int.Parse("1"));

Find duplicates, concatenate cells


 Hello all,

I have a database where I need to find duplicate rows based on one cell and concatenate the two cells from both into one record. For example i'll have:


ID             NameOne               IDNumber

1              John                        2983

2              Jones                      2983

3              James                     2984


I'm wondering if there is a SQL statement where I can search for the duplicates of "2983" and come out with a record of:

ID             NameOne              

Chart Control getting numbers from GridView, but just duplicates


I have a gridview that I loop through to get the x and y values of the chart control, and upon the first execution of this code, it looks great.  But when the page posts back and it "refreshes" data, the original points on the chart control are still there, as well as the new values.

Is there any way to "delete" all data in the chart control to ensure this doesn't happen?  I can't seem to find any resources to help me out with this.

Thanks in advance. :)

A tidy and efficient way of excluding rows with the same column value or equal to null


Good morning

I frequently use the pivot statement to generate columns I wish to compare, often looking for differences between the values in the columns. Quite often the pivot returns some null values which I am having some difficulty in comparing.


Rownum              Col1       col2        col3    col4  etc

1                              A             A             A     &



hello All ,

I have a result set with 3 columns  called account number , codebf,codeaf . for same account number i have different codeaf and codeaf

Example :

123    5   0

123    3  8


I need to select only one . How can i do that ?....anyone is fine ..thanks...

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend