[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