[Babase] Re: query from Marie
Lacey Maryott
lacey.maryott at duke.edu
Thu May 10 16:02:03 EDT 2007
Hello again,
we tried these queries, and they were returning every line for the
cids.... soo we tried to do count(distinct(cycpoints.cid)) and tried
adding in "order by members.grp, members.date"... however it still seems
to be taking a VERY long time in postgres before it switches to apache
and loads into the browser. right now, the query, with the addition of
distinct and order by has been running in postgres 5 minutes. IT isn't
terribly long, just long enough to be a kind of a pain. Does anyone else
have any suggestions for what might work?
Thanks!!
Lacey
Lacey Maryott wrote:
> Karl,
> Marie says thank you very much! and so do I! :)
>
> Lacey
>
> Karl O. Pinc wrote:
>> No reason not to use the list for this so everybody
>> can see....
>>
>> On 05/10/2007 01:48:22 PM, Lacey Maryott wrote:
>>> select count(cycpoints.cid), members.grp,
>>> date_part('month', members.date) as month,
>>> date_part('year', members.date) as year from
>>> cycpoints, cycles, members where members.sname =
>>> cycles.sname and cycles.cid = cycpoints.cid and
>>> cycpoints.code='D' group by members.grp, members.date;
>>>
>>> it finally loaded for her the second we got off the phone with
>>> you.... but it gave some WEIRD info... she is looking for the number
>>> of females who had a "D" code for their cycle in every month for
>>> like.... forever. Ambitious I know. Thanks so much for looking at
>>> this, we both really appreciate it.
>>
>> She probably wants:
>>
>> select count(cycpoints.cid), members.grp,
>> date_part('month', members.date) as month,
>> date_part('year', members.date) as year from
>> cycpoints, cycles, members where members.sname =
>> cycles.sname and cycles.cid = cycpoints.cid and
>> cycpoints.code='D' group by members.grp, month, year;
>>
>> Which is the same as:
>>
>> select count(cycpoints.cid), members.grp,
>> date_part('month', members.date) as month,
>> date_part('year', members.date) as year from
>> cycpoints, cycles, members where members.sname =
>> cycles.sname and cycles.cid = cycpoints.cid and
>> cycpoints.code='D' group by members.grp,
>> date_part('month', members.date),
>> date_part('year', members.date);
>>
>> (I don't know just when you can use "foo"
>> when you rename columns with "AS foo", but
>> it seems to always work with the GROUP BY.)
>>
>> It's also the same as using count(*) to count rows:
>>
>> select count(*), members.grp,
>> date_part('month', members.date) as month,
>> date_part('year', members.date) as year from
>> cycpoints, cycles, members where members.sname =
>> cycles.sname and cycles.cid = cycpoints.cid and
>> cycpoints.code='D' group by members.grp, month, year;
>>
>> You could also use the view that already joins
>> cycpoints and cycles, but if you don't it
>> can be marginally faster under some conditions:
>>
>> select count(cycpoints_cycles.cid)
>> , members.grp
>> , date_part('month', members.date) as month
>> , date_part('year', members.date) as year
>> from cycpoints_cycles, members
>> where members.sname = cypoints_cycles.sname
>> and cycpoints_cycles.code='D'
>> group by members.grp, month, year;
>>
>> (I find formatting the SQL makes it easier
>> to read.)
>>
>> Karl <kop at meme.com>
>> Free Software: "You don't pay back, you pay forward."
>> -- Robert A. Heinlein
>>
>> _______________________________________________
>> Babase mailing list
>> Babase at www.eco.princeton.edu
>> http://www.eco.princeton.edu/mailman/listinfo/babase
>>
>
--
Lacey Maryott
Alberts Lab
Department of Biology
Duke University
ph: 919-660-7306
fax: 919-660-7293
Lacey.Maryott at duke.edu
More information about the Babase
mailing list