[Babase] Another REPSTATS/CYCSTATS question

Karl O. Pinc kop at meme.com
Thu Jun 8 11:41:42 EDT 2006


On 06/08/2006 09:57:22 AM, Catherine Markham wrote:
> Hi Karl,
> 
> Maybe this isn't anything important (hopefully so), but I was  
> checking whether a female had any rows in either REPSTATS or CYCSTATS  
> after her statdate (both tables were fine with regard to this).  Then  
> I decided to check whether a female had any rows actually on her  
> statdate - in REPSTATS, there were plenty of matches but none in  
> CYCSTATS.  I double checked and looked only at rows in REPSTATS where  
> REPSTATS.date = BIOGRAPH.statdate and REPSTATS.state = C... still  
> nothing in CYCSTATS for that particular day.

Either REPSTATS or CYCSTATS is wrong, or both.  I don't want to think
about it this instant.  Do you have an opinion about whether either
is right?

babase=> select repstats.state, count(*) from repstats, cycstats where  
repstats.sname = cycstats.sname and repstats.date = cycstats.date and  
repstats.state != 'C' group by repstats.state order by repstats.state;
  state | count
-------+-------
  L     |  1412
  P     |   196
(2 rows)

babase=> select count(*) from repstats where not exists (select 1 from  
cycstats where cycstats.sname = repstats.sname and cycstats.date =  
repstats.date) and repstats.state = 'C' order by repstats.sname,  
repstats.date;
ERROR:  column "repstats.sname" must appear in the GROUP BY clause or  
be used in an aggregate function
babase=> select count(*) from repstats where not exists (select 1 from  
cycstats where cycstats.sname = repstats.sname and cycstats.date =  
repstats.date) and repstats.state = 'C';
  count
-------
   4651
(1 row)


Karl <kop at meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein



More information about the Babase mailing list