Обсуждение: joining 2 Tables.
Hallo,
trying to join these two tables.
SELECT to_timestamp(trunc(date_part('epoch', updatezeit)/600) * 600) at time zone 'mez' AS updatezeit,
count(*) as thecount,
round (cast(avg (value)as numeric),1) as avg,
round (cast(min (value)as numeric),1) as min,
round (cast(max (value)as numeric),1) as max
FROM variablen WHERE updatezeit >= '12.03.2014 00:00:00' AND varid = '35177'
GROUP BY to_timestamp(trunc(date_part('epoch', updatezeit)/600) * 600) at time zone 'mez'
ORDER BY updatezeit
and
SELECT foo.updatezeit
FROM (select ('12.03.2014 00:00:00'::timestamp + s * '10minutes'::interval) AS updatezeit
FROM generate_Series(0,4000)s) as foo
by using this code
SELECT
foo.updatezeit
FROM (select ('12.03.2014 00:00:00'::timestamp + s * '10minutes'::interval) AS updatezeit
FROM generate_Series(0,4000)s) as foo
left join
(
SELECT to_timestamp(trunc(date_part('epoch', updatezeit)/600) * 600) at time zone 'mez' AS updatezeit,
count(*) as thecount,
round (cast(avg (value)as numeric),1) as avg,
round (cast(min (value)as numeric),1) as min,
round (cast(max (value)as numeric),1) as max
FROM variablen WHERE updatezeit >= '12.03.2014 00:00:00' AND varid = '35177'
GROUP BY to_timestamp(trunc(date_part('epoch', updatezeit)/600) * 600) at time zone 'mez'
ORDER BY updatezeit
) as n2
using(updatezeit)
group by 1
order by 1
but the result is only the left table.
regarts
Thomas
Thomas Drebert wrote > SELECT > foo.updatezeit > FROM You joined them ok. But this says you only want to see the foo.updatezeit in the final output. Try * David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/joining-2-Tables-tp5796290p5796328.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Hello,
if i use the *, I get an error message.
ERROR: column "n2.thecount" must appear in the GROUP BY clause or be used in an aggregate function
********** Fehler **********
ERROR: column "n2.thecount" must appear in the GROUP BY clause or be used in an aggregate function
SQL Status:42803
regarts
Thomas
2014-03-17 14:52 GMT+01:00 David Johnston <polobo@yahoo.com>:
Thomas Drebert wrote
> SELECT
> foo.updatezeit
> FROM
You joined them ok. But this says you only want to see the foo.updatezeit
in the final output. Try *
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/joining-2-Tables-tp5796290p5796328.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Thomas Drebert wrote > Hello, > > if i use the *, I get an error message. > > ERROR: column "n2.thecount" must appear in the GROUP BY clause or be used > in an aggregate function > ********** Fehler ********** > > ERROR: column "n2.thecount" must appear in the GROUP BY clause or be used > in an aggregate function > SQL Status:42803 > > regarts > Thomas > > > > 2014-03-17 14:52 GMT+01:00 David Johnston < > polobo@ > >: > >> Thomas Drebert wrote >> > SELECT >> > foo.updatezeit >> > FROM >> >> You joined them ok. But this says you only want to see the >> foo.updatezeit >> in the final output. Try * >> >> David J. > >> Sorry, "*" won't work because of the group/order at that level of the query. You will need to devise a combination of select-list columns and group-by columns that will give you the results you are looking for. If you actually tell us what you want you will likely get better answers. To be honest I'm not sure why you even have GROUP BY 1 in the query in the first place. Remove that and the "*" will work. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/joining-2-Tables-tp5796290p5796463.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.