Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: How do you SUM the fields retrieved by COUNT?

    I am trying to do this in MS Access actually but it is an SQL question.

    This query:

    SELECT LoanNo, COUNT(LoanNo) AS 'Count'
    FROM DedupTest031504
    GROUP BY LoanNo
    HAVING COUNT(LoanNo) > 1;


    returns:
    LoanNo Count
    46690128 2
    46861821 2
    47762138 3
    47762154 3
    48257239 2
    48257663 2
    48257719 2
    48258143 2
    48258167 2

    which is correct but how do you SUM the COUNT field? In other words, I want the total number of duplicate records in the table. Is there another way alltogether?

    I tried:

    SELECT LoanNo, SUM(COUNT(LoanNo))
    FROM DedupTest031504
    GROUP BY LoanNo
    HAVING COUNT(LoanNo) > 1;

    but I get a "CANNOT HAVE AGGREGATE FUNCTION IN 'SUM(COUNT(LoanNo))' error.

    Thanks.

    ddave

  2. #2
    Join Date
    Mar 2004
    Posts
    80

    Re: How do you SUM the fields retrieved by COUNT?

    try this.
    ignore this if this not the way u r expecting .

    -----------------------------------------------
    select LoanNo,sum(count) as count from (SELECT LoanNo, COUNT(LoanNo) AS 'Count'
    FROM DedupTest031504
    GROUP BY LoanNo
    HAVING COUNT(LoanNo) > 1) as somename
    -----------------------------------------------
    Originally posted by dolfandave
    I am trying to do this in MS Access actually but it is an SQL question.

    This query:

    SELECT LoanNo, COUNT(LoanNo) AS 'Count'
    FROM DedupTest031504
    GROUP BY LoanNo
    HAVING COUNT(LoanNo) > 1;


    returns:
    LoanNo Count
    46690128 2
    46861821 2
    47762138 3
    47762154 3
    48257239 2
    48257663 2
    48257719 2
    48258143 2
    48258167 2

    which is correct but how do you SUM the COUNT field? In other words, I want the total number of duplicate records in the table. Is there another way alltogether?

    I tried:

    SELECT LoanNo, SUM(COUNT(LoanNo))
    FROM DedupTest031504
    GROUP BY LoanNo
    HAVING COUNT(LoanNo) > 1;

    but I get a "CANNOT HAVE AGGREGATE FUNCTION IN 'SUM(COUNT(LoanNo))' error.

    Thanks.

    ddave
    Last edited by theguru; 03-15-04 at 14:08.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    PHP Code:
    SELECT LoanNoCOUNT(LoanNo) AS 'Count'
    FROM DedupTest031504
    GROUP BY LoanNo
    HAVING COUNT
    (LoanNo) > 1
    COMPUTE Sum
    (Count(LoanNo)); 
    But then again, I'm a lazy bum!

    -PatP

  4. #4
    Join Date
    Feb 2004
    Posts
    193
    That didn't work, Pat, unless I am still doing something wrong. Are you sure MS Access takes the COMPUTE keyword?

    I tried:

    SELECT COUNT(LoanNo) AS 'Count'
    FROM (SELECT LoanNo, COUNT(LoanNo) AS 'Count'
    FROM DedupTest031504
    GROUP BY LoanNo
    HAVING COUNT(LoanNo) > 1);

    and got back 29 records which is correct in terms of loan numbers with MORE THAN 1 RECORD. However, two of the 29 have 3 records each. I want to return a value that represents the TOTAL number of duplicates including those in triplicate, etc. but I can't SUM the "COUNT(LoanNo)" in the subquery.

    If I have 29 records and 27 records have a COUNT of 2 and 2 records have a COUNT of 3 then

    27 * 2 = 54
    2 * 3 = 6

    So the total I am looking for is 60.

    Thanks again for the trouble.

    ddave


    Originally posted by Pat Phelan
    I'd use:
    PHP Code:
    SELECT LoanNoCOUNT(LoanNo) AS 'Count'
    FROM DedupTest031504
    GROUP BY LoanNo
    HAVING COUNT
    (LoanNo) > 1
    COMPUTE Sum
    (Count(LoanNo)); 
    But then again, I'm a lazy bum!

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What version of Access?

    Code:
    SELECT SUM([Count]) FROM (
    SELECT LoanNo, COUNT(LoanNo) AS 'Count'
    FROM DedupTest031504
    GROUP BY LoanNo
    HAVING COUNT(LoanNo) > 1) AS A;
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Posts
    193
    Originally posted by Brett Kaiser
    What version of Access?

    Code:
    SELECT SUM([Count]) FROM (
    SELECT LoanNo, COUNT(LoanNo) AS 'Count'
    FROM DedupTest031504
    GROUP BY LoanNo
    HAVING COUNT(LoanNo) > 1) AS A;
    MS Access 2002. Thanks for looking at it, buddy.

    ddave

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, I was thinking you were using passthrough to MS-SQL. In Jet, I'd use:
    PHP Code:
    SELECT Sum(c)
       
    FROM (SELECT LoanNoCOUNT(LoanNo) AS c
          FROM DedupTest031504
          GROUP BY LoanNo
          HAVING COUNT
    (LoanNo) > 1) AS b
    This basically makes your query a subquery, then sums up the counts in the outer query.

    -PatP

  8. #8
    Join Date
    Feb 2004
    Posts
    193
    OK,

    I will give it a shot and post the response. Thanks again.

    ddave

    Originally posted by Pat Phelan
    Sorry, I was thinking you were using passthrough to MS-SQL. In Jet, I'd use:
    PHP Code:
    SELECT Sum(c)
       
    FROM (SELECT LoanNoCOUNT(LoanNo) AS c
          FROM DedupTest031504
          GROUP BY LoanNo
          HAVING COUNT
    (LoanNo) > 1) AS b
    This basically makes your query a subquery, then sums up the counts in the outer query.

    -PatP

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    These queries appear to be giving the total number of records for which a duplicate exists. Perhaps that is what you want, but it is not quite the same as asking for the number of duplicate records.
    If you just sum the counts of all records that have been duplicated, you will need to subtract the count of all records that have been duplicated, because if you get a count of, say, 3, then actually only 2 of those are duplicates.

    To get the number of duplicate records, or the number of records that would have to be eliminated from the table to make all the records unique, use a query like this:

    select count(*) - count(distinct LoanNo) from DedupTest031504
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Pat Phelan
    Sorry, I was thinking you were using passthrough to MS-SQL. In Jet, I'd use:
    PHP Code:
    SELECT Sum(c)
       
    FROM (SELECT LoanNoCOUNT(LoanNo) AS c
          FROM DedupTest031504
          GROUP BY LoanNo
          HAVING COUNT
    (LoanNo) > 1) AS b
    This basically makes your query a subquery, then sums up the counts in the outer query.

    -PatP
    Gee...that looks awfully familiar for some reason....

    Yo blind dude....nice one...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Brett Kaiser
    Gee...that looks awfully familiar for some reason....
    Oops! I really need to reread the thread before I reply instead of just replying to the posting that catches my attention. Sorry!

    You and Blindman are correct, there is a difference between the total number of duplicate rows (Sum(c)), duplicate keys (Count(*)), and extraneous rows(Sum(c) - Count(*)).

    -PatP

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It's simple code, if that is what he really wants. He hasn't said why he wants this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Posts
    193

    Talking

    That did the trick, Pat. I got exactly 60 records, just like I needed. Thanks a bunch. See, you're not so lazy afterall.

    ddave


    Originally posted by Pat Phelan
    Sorry, I was thinking you were using passthrough to MS-SQL. In Jet, I'd use:
    PHP Code:
    SELECT Sum(c)
       
    FROM (SELECT LoanNoCOUNT(LoanNo) AS c
          FROM DedupTest031504
          GROUP BY LoanNo
          HAVING COUNT
    (LoanNo) > 1) AS b
    This basically makes your query a subquery, then sums up the counts in the outer query.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •