Sql redundant values (1 Viewer)

[Joy83]

Hi

I have a table with duplicate values.
What is the sql that gets me the result 5 (total of values of redundant fields) from below table.

And is there any way to get the redundant values only without keeping the original.
In the case below, how to get the result 3

Number:
8
9
9
9
2
2
1
5
3

 

[theDBguy]

If I understand your first question correctly, the result is 5 because of 9,9,9,2, and 2. Right?

If so, I'm not sure I understand the second question. How is the result = 3?

 

[Joy83]

If I understand your first question correctly, the result is 5 because of 9,9,9,2, and 2. Right?

If so, I'm not sure I understand the second question. How is the result = 3?

yes right for the first one

the second one
You count only the duplicates without the originals
So you exclude one 9, one 2
And you count the duplicated values of them


The first sql will do

 

[theDBguy]

yes right for the first one

the second one
You count only the duplicates without the originals
So you exclude one 9, one 2
And you count the duplicated values of them


The first sql will do

Hi. Thanks for the clarification. If you only want to use SQL, it might get a little convoluted. For example, this might give you the 5 result:

SELECT Sum(SQ.TotalCount) AS Result
FROM (SELECT T1.Number, Count(*) AS TotalCount
    FROM TableName T1
    GROUP BY T1.Number
    HAVING Count(*)>1) SQ

(untested)
Edit: Added HAVING clause. Thanks to @CJ_London

 

[CJ_London]

@theDBguy think your aliased query needs a

Having Count(*)>1

 

[theDBguy]

@theDBguy think your aliased query needs a

Having Count(*)>1

Oh, sorry I forgot that. Thanks for reminding me.

 

[CJ_London]

This query gives you both answers - change names to suit

SELECT Sum(Q.CountOfnum) AS SumOfCountOfnum, Sum([countofnum])-Count(*) AS Expr1
FROM (SELECT Table5.num, Count(Table5.num) AS CountOfnum
FROM Table5
GROUP BY Table5.num
HAVING (((Count(Table5.num))>1)))  AS Q

from your supplied data gives this result
Query43 Query43

SumOfCountOfnum	Expr1
5​	3​

 

[arnelgp]

you can also use:

SELECT yourTTable.Number
FROM YOURTable Where DCount("1", "yourTable", "Number = " & [Number]) > 1

 

[Joy83]

Thanks all
It worked I used this one:

SELECT Sum(Q.CountOfnum) AS SumOfCountOfnum, Sum([countofnum])-Count(*) AS Expr1
FROM (SELECT Table5.num, Count(Table5.num) AS CountOfnum
FROM Table5
GROUP BY Table5.num
HAVING (((Count(Table5.num))>1))) AS Q

 

[theDBguy]

Thanks all
It worked I used this one:

SELECT Sum(Q.CountOfnum) AS SumOfCountOfnum, Sum([countofnum])-Count(*) AS Expr1
FROM (SELECT Table5.num, Count(Table5.num) AS CountOfnum
FROM Table5
GROUP BY Table5.num
HAVING (((Count(Table5.num))>1))) AS Q

Glad to hear you got it sorted out. Good luck with your project.