Thread: Question re 2 aggregates from 1 query

Assume a simple many-to-one between 2 tables:

 create table docs (id int8 primary key, timestamp imported_when);
 create table pages (id int8 primary key, doc_id int8 not null references
docs);

It's easy to get a count of docs imported by date:

 select imported_when::date, count(1)
 from docs
 group by imported_when::date;

It's easy to get a count of pages imported by date:

 select imported_when::date, count(1)
 from docs, pages
 where docs.id = pages.doc_id
 group by imported_when::date;

Is there any way to get count of docs & pages imported by date without
resorting to selecting from a select:

 select dt, count(1), numpgs from (
   select docs.imported_when::date as dt, count(1) as numpgs
   from docs, pages
   where docs.id = pages.doc_id
   group by docs.imported_when::date, docs.id
) as t1 group by dt order by dt;

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Yes, obviously that should have been sum(numpgs) in the select list of the
last query... Question remains the same...

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

>
> Is there any way to get count of docs & pages imported by date without
> resorting to selecting from a select:

[Spotts, Christopher]
If I understand you correctly..?

create table docs (id int8 primary key, imported_when timestamp );
create table pages (id int8 primary key, doc_id int8 not null references
docs);
INSERT INTO docs values (1,now()),(2,now()),(3,now()),(4,now()- interval '1
day');
INSERT INTO pages values (1,1),(2,1),(3,2),(4,2),(5,3),(6,4),(7,4),(8,4);

select imported_when::date, count(distinct pages.id),count(distinct docs.id)
 from docs, pages
 where docs.id = pages.doc_id
 group by imported_when::date;

> If I understand you correctly..?

Yes, exactly! I think I was suffering from a flashback to a very old DBMS
that didn't support that use of distinct...

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice