From: | Meike Börder <boerder(at)uni-landau(dot)de> |
---|---|
To: | "'Thom Brown'" <thom(at)linux(dot)com> |
Cc: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Postgresql-query for absolute beginner |
Date: | 2012-08-20 11:41:31 |
Message-ID: | 002d01cd7ec8$bedbd790$3c9386b0$@de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
thanks a lot, that worked!
cheers, Meike
-----Ursprüngliche Nachricht-----
Von: thombrown(at)gmail(dot)com [mailto:thombrown(at)gmail(dot)com] Im Auftrag von Thom
Brown
Gesendet: Montag, 20. August 2012 13:35
An: Meike Börder
Cc: pgsql-novice(at)postgresql(dot)org
Betreff: Re: [NOVICE] Postgresql-query for absolute beginner
On 20 August 2012 11:55, Meike Börder <boerder(at)uni-landau(dot)de> wrote:
> Hey folks,
>
>
>
> as you may have guessed from the title, Im an absolute beginner using
> PostgreSQL and I need some help. I have a table with the columns country
> (text), sample site (text) and date (timestamp with time zone):
>
> country sample site date
>
> AA a1 01.01.2000
>
> AA a1 12.02.1999
>
> AA a2 01.01.2000
>
> BB b1 23.04.1984
>
> BB b2 05.05.2000
>
> BB b3 01.01.2000
>
> CC c1 15.03.1998
>
> CC c1 24.10.2002
>
> CC c2 15.03.1998
>
>
>
> What I want to know now is how often the different sampling sites were
> tested. How do I have to write my query? I tried using something like
>
> Select country, sample site, count (date) from samples
>
> But the result was rubbish. I could Imagine that I have to create a
> loop for this query (the table has overall more than 1 mio rows) to
> get a result table with a list of sampling sites and the number of
> surveys for each of them.
Did you use a GROUP BY?
SELECT country, sample_site, count(date) FROM samples GROUP BY country,
sample_site;
A GROUP BY is required when using aggregate functions, of which "count" is
one.
--
Thom
From | Date | Subject | |
---|---|---|---|
Next Message | Gavin Flower | 2012-08-20 11:47:49 | Re: Postgresql-query for absolute beginner |
Previous Message | Oliveiros d'Azevedo Cristina | 2012-08-20 11:38:58 | Re: Postgresql-query for absolute beginner |