Re: Postgresql-query for absolute beginner

On 20/08/12 23:38, Oliveiros d'Azevedo Cristina wrote:
> Howdy, Meike.
> Not sure if this is what you want, but
> if you write
> SELECT "sample site" , COUNT("date")
> FROM samples
> GROUP BY "sample site"
> It will return the number of surveys for each one of the site.
> Ain't I right?
> Best,
> Oliver
>
> ----- Original Message -----
> *From:* Meike Börder <mailto:boerder(at)uni-landau(dot)de>
> *To:* pgsql-novice(at)postgresql(dot)org
> <mailto:pgsql-novice(at)postgresql(dot)org>
> *Sent:* Monday, August 20, 2012 11:55 AM
> *Subject:* [NOVICE] Postgresql-query for absolute beginner
>
> 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.
>
> Help would be greatly appreciated!
>
> Yours, Meike
>
You're assuming that a 'sample site' value is either unique or that the
count for a site should include all associated countries.

You may, or may not, be right! :-)

Anyhow, please don't top post.

Cheers,
Gavin