Skip site navigation (1) Skip section navigation (2)

Re: Postgresql-query for absolute beginner

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 (view raw or flat)
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, 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



In response to

pgsql-novice by date

Next:From: Gavin FlowerDate: 2012-08-20 11:47:49
Subject: Re: Postgresql-query for absolute beginner
Previous:From: Oliveiros d'Azevedo CristinaDate: 2012-08-20 11:38:58
Subject: Re: Postgresql-query for absolute beginner

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group