On 20 August 2012 11:55, Meike Börder <boerder@uni-landau.de> wrote:
> Hey folks,
>
>
>
> as you may have guessed from the title, I’m 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