Hi,

I need to retrieve
1. a total
SELECT SUM(COL1) FROM SOME_TABLE
2. most frequent values
SELECT COL1, COUNT(*) FROM SOME_TABLE GROUP BY COL1

I want to scan the table only once, so I was trying to accomplish both
tasks in one statement.
Any ideas?
TIA

Ford Desperado wrote:
You can retrieve both results with a single query, but you can't with a single table
scan.

--
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
================================================== ============
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Lee,

how could such a query look like (and don't tell me about UNION ALL)?

Ford Desperado wrote:
UNION with or (preferably) without ALL is one choice. A second choice is:

SELECT COL1, COUNT(*), (SELECT SUM(COL1) FROM SOME_TABLE)
FROM SOME_TABLE GROUP BY COL1

--
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
================================================== ============
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

You haven't mentioned which database. So I'm assuming it's ANSI-92 compliant.

SELECT S1.COL1
,S2.SUM_AMT
, COUNT(*)
FROM SOME_TABLE as S1
,(SELECT SUM(COL1) as SUM_AMT
FROM SOME_TABLE) as S1
GROUP BY S1.COL1
,S2.SUM_AMT;

As Lee pointed out it won't happen in a single scan.

Christian.

christian.maslen (AT) techie (DOT) com (Christian Maslen) wrote in message news:<b9c8cfba.0402230001.5fb27d77 (AT) posting (DOT) google.com>...
A single scan is possible in Oracle, using a non-ANSI extension to SQL:

SQL> select deptno, count(*), sum(count(*)) over ()
2 from emp
3 group by deptno;

DEPTNO COUNT(*) SUM(COUNT(*))OVER()
---------- ---------- -------------------
10 3 14
20 5 14
30 6 14


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=3 Bytes=6)
1 0 WINDOW (BUFFER)
2 1 SORT (GROUP BY) (Cost=54 Card=3 Bytes=6)
3 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=28)




Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
0 physical reads
0 redo size
509 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
3 rows processed

Lee Fesperman <firstsql (AT) ix (DOT) netcom.com> writes:

He didn't say he wanted a single table scan; he said he only wanted to
scan SOME_TABLE once. The latter is possible.

Question for the original poster: You want to scan the table only once
because you're encountering performance problems, or because this is a
homework problem for a class?

"Tony" <andrewst (AT) onetel (DOT) net.uk> wrote

If he were summing counts and not col1's, that might actually work.

Thanks everybody for answering,

the answer is

SELECT COUNT(*), SUM(COLVALUE), COLVALUE
FROM ZTABLE_COLUMNS2
GROUP BY GROUPING SETS(COLNUM, (COLNUM, COLVALUE))

performs much much better than 2 statements, because a big huge table
is scanned just once.
Also: it's Oracle 9i.

Analytical or windowing functionality is part of the upcoming ANSI
standard (2003 maybe?). It is also available in DB2 and is coming to
Yukon. Either way the functionality will only window on the current
result set, not the underlying table.

So the following will not give the original poster what they wanted:

create table some_table(
col1 integer not null
);

Displaying result for:
---------------------
insert into some_table
values (15),(20),(25),(45)

4 Row(s) affected

Displaying result for:
---------------------
insert into some_table
values (15),(20),(25),(45)

4 Row(s) affected

Displaying result for:
---------------------
select sum(col1)
from some_table

1
-----------
210

1 Row(s) affected

Displaying result for:
---------------------
select col1
, count(*)
, sum(col1) over() as summ_all
from some_table
group by col1

COL1 2 SUMM_ALL
----------- ----------- -----------
15 2 105
20 2 105
25 2 105
45 2 105

4 Row(s) affected


Christian.