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

Re: Modifying selected records

From: "Oliveiros C," <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Ruzsinszky Attila" <ruzsinszky(dot)attila(at)gmail(dot)com>
Cc: "postgresql novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Modifying selected records
Date: 2009-09-02 12:39:14
Message-ID: ADC66B15D65D4A379832779585B26CFF@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-novice
I see.
I completely 4got  the GROUP BY clause

Add
GROUP BY query1.sorszam, query1.all_kod, query1.nev, query1.megall, 
query1.erkezik_ido, query1.indul_ido, query1.train_selector

to the query I gave you.
And try again

Best,
Oliveiros


----- Original Message ----- 
From: "Ruzsinszky Attila" <ruzsinszky(dot)attila(at)gmail(dot)com>
To: "Oliveiros C," <oliveiros(dot)cristina(at)marktest(dot)pt>
Cc: "postgresql novice" <pgsql-novice(at)postgresql(dot)org>
Sent: Wednesday, September 02, 2009 1:10 PM
Subject: Re: [NOVICE] Modifying selected records


> SELECT COUNT(query2.*),
> query1.all_kod,quey1.nev,query1.megall,query1.erkezik_ido,query1.indul_ido,'910_'
> || COUNT(query2.*)
> FROM (/*your query goes here */) query1, (/*and here */) query2
> WHERE query1.indul_ido >= query2.indul_ido

Here is my SELECT first:

SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido, train_selector
 FROM menetrend, stat  where statkod=all_kod and
train_selector in (SELECT train_selector
  FROM service where vonatszam='910' and datum_ig>now() and
datum_tol<now() order by datum_ig desc, datum_tol desc) order by
indul_ido

When I run that I got what I put here.


After this I combined your version with my select:
( I put my SELECT mechanically twice in /*your query goes here */ parts.)

SELECT  COUNT(query2.*),
query1.all_kod,query1.nev,query1.megall,query1.erkezik_ido,query1.indul_ido,'910_'
|| COUNT(query2.*)
FROM (SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido,
train_selector
 FROM menetrend, stat  where statkod=all_kod and
train_selector in (SELECT train_selector
  FROM service where vonatszam='910' and datum_ig>now() and
datum_tol<now() order by datum_ig desc, datum_tol desc) order by
indul_ido) query1, (SELECT sorszam, all_kod, nev, megall, erkezik_ido,
indul_ido, train_selector
 FROM menetrend, stat  where statkod=all_kod and
train_selector in (SELECT train_selector
  FROM service where vonatszam='910' and datum_ig>now() and
datum_tol<now() order by datum_ig desc, datum_tol desc) order by
indul_ido) query2
WHERE query1.indul_ido >= query2.indul_ido

The result is:
ERROR:  column "query1.all_kod" must appear in the GROUP BY clause or
be used in an aggregate function

In my SELECT only one train was selected.

TIA,
Ruzsi 


In response to

Responses

pgsql-novice by date

Next:From: Ruzsinszky AttilaDate: 2009-09-02 16:28:07
Subject: Re: Modifying selected records
Previous:From: Ruzsinszky AttilaDate: 2009-09-02 12:10:36
Subject: Re: Modifying selected records

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