SQL help

[puneet]

HI

I have a table in which the serial numbers and the totals are being repeated . I want to get the count of distinct rows as well as the total of those rows.

Let me know how can i write it

eg

SNO TOTAL

1 10
1 10
2 5
3 10
3 10

i want distinct count fo rows and total for them is same
SQL

answer I am expecting in this case is

3 25

let me know

[vbaskar]

hi,

first of all u ve not mentioned the version currently u r working with...but i can give u the solution on oracle 8.0.5.

say u have 2 colums slno, total

SQLWKS> select * from test
2>
SLNO TOTAL
---------- ----------
1 10
1 10
2 5
3 10
3 10

5 Rows Selected

--query

select (select count(bb) from(select count(slno) bb from test group by slno)) count,
bb total
from
(select
sum(total) bb
from
test
where
rowid in (select max(rowid) from test
group by slno)
)


--output

COUNT TOTAL
---------- ----------
3 25

1 Row Selected..

[chrisrlong]

There is a reply in the Admin forum to this same question that is much more efficient. Check there.

- Chris