Thread: Postgresql-query for absolute beginner

Postgresql-query for absolute beginner

From

Meike Börder

Date:

20 August 2012, 14:33:05

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

Re: Postgresql-query for absolute beginner

From

Thom Brown

Date:

20 August 2012, 14:35:55

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

Re: Postgresql-query for absolute beginner

From

"Oliveiros d'Azevedo Cristina"

Date:

20 August 2012, 14:39:09

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
To: pgsql-novice@postgresql.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, 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.

Help would be greatly appreciated!

Yours, Meike

Re: Postgresql-query for absolute beginner

From

Meike Börder

Date:

20 August 2012, 14:46:51

thanks a lot, that worked!

cheers, Meike


-----Ursprüngliche Nachricht-----
Von: thombrown@gmail.com [mailto:thombrown@gmail.com] Im Auftrag von Thom
Brown
Gesendet: Montag, 20. August 2012 13:35
An: Meike Börder
Cc: pgsql-novice@postgresql.org
Betreff: Re: [NOVICE] Postgresql-query for absolute beginner

On 20 August 2012 11:55, Meike Börder <boerder@uni-landau.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

Re: Postgresql-query for absolute beginner

From

Gavin Flower

Date:

20 August 2012, 14:50:19

On 20/08/12 23:38, Oliveiros d'Azevedo Cristina wrote:

@font-face {font-family: Cambria Math; } @font-face {font-family: Calibri; } @page WordSection1 {size: 612.0pt 792.0pt; margin: 70.85pt 70.85pt 2.0cm 70.85pt; } P.MsoNormal {MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Calibri","sans-serif"; FONT-SIZE: 11pt } LI.MsoNormal {MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Calibri","sans-serif"; FONT-SIZE: 11pt } DIV.MsoNormal {MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Calibri","sans-serif"; FONT-SIZE: 11pt } A:link {COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99 } SPAN.MsoHyperlink {COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99 } A:visited {COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99 } SPAN.MsoHyperlinkFollowed {COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99 } SPAN.E-MailFormatvorlage17 {FONT-FAMILY: "Calibri","sans-serif"; COLOR: windowtext; mso-style-type: personal-compose } .MsoChpDefault {FONT-SIZE: 10pt; mso-style-type: export-only } DIV.WordSection1 {page: WordSection1 }
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
To: pgsql-novice@postgresql.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

Re: Postgresql-query for absolute beginner

From

"Oliveiros d'Azevedo Cristina"

Date:

20 August 2012, 15:10:01

----- Original Message -----

From: Meike Börder
To: pgsql-novice@postgresql.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, 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.
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.

* Yeah, that wouldn't work if two sample sites on different countries happen to have the same name, but from the sample data nothing made me think so, as all sample site ID begin with the same letter as the associated country

Any way, thanks for pointin that out.

Greetings from up over,
Oliver

Cheers,
Gavin