Re: Postgresql-query for absolute beginner - Mailing list pgsql-novice

@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