| Total of Count [message #4829] |
Thu, 16 January 2003 09:41  |
Lard
Messages: 1
Registered: January 2003
|
Junior Member |
|
|
I have a query that finds the count of the number of duplicate account#. The query has a count for each specific account#. I am wondering if there is a way to get a total of those counts within the same query?
Thanks
|
|
|
|
| Re: Total of Count [message #4839 is a reply to message #4829] |
Thu, 16 January 2003 15:08  |
ramesh
Messages: 123
Registered: August 2000
|
Senior Member |
|
|
Try to use having (count) in your where clause
eg,
select acct_num from acct_table
having count(acct_num)> 1
group by acct_num;
this will list all acct_num that appears more than once.
|
|
|
|
|
|
| Re: Total of Count [message #4852 is a reply to message #4829] |
Fri, 17 January 2003 06:56 |
Joyjeet
Messages: 23
Registered: January 2003
|
Junior Member |
|
|
If I understood yr prob correctly, then it's as follow:
Table T1 having duplicate values in col B:
A B
---------- ----------
1 2
2 2
3 5
4 5
5 7
6 8
7 9
8 9
9 9
U got the output:
select b,count(b) from t1 group by b having count(b) > 1;
B COUNT(B)
----- ----------
2 2
5 2
9 3
U need the output :
B COUNT(B) TOTAL-DUP
---------- ---------- ----------
2 2 7
5 2 7
9 3 7
If u r working with DB prior to 9i then the SQL should be:
select a.b,a.c,b.a
from (select b,count(b) c from t1 group by b having count(b) > 1) a,
(select sum(d) a from (select count(b) d from t1 group by b having count(b) > 1)) b
If u r working with 9i DB then rollup will be handy.
keep in touch.
JD
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
