Re: [SQL] Group by and aggregates

[Sam Mason]

Michael L. Hostbaek wrote:
>Now, I need the first line to say "15.00" in the cmup field. That is,
>stock and incoming are obviously not being grouped, but since it's the
>same partno I'd like somehow to show the highest cmup. Is there some
>black SQL voodoo that'll achieve this ?

I think you need to join the table back on itself to get the total
for each part.  Not a very impressive incantation, but I'd do it 
in SQL like this:

  SELECT x.partno, x.status, y.tot, sum(x.qty)
  FROM my_table x, (
SELECT partno, MAX(cmup) as tot
FROM my_table
GROUP BY partno) y
  WHERE x.partno = y.partno
  GROUP BY x.partno, x.status, y.tot

Cheers,
  Sam

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Comparing two (largish) tables on different servers

[Sam Mason]

Gregory S. Williamson wrote:
>Is there any way to do this from inside postgres that anyone knows of
>? I looked through the manual and the contrib stuff and didn't see
>much ...

Not really "inside postgres"; but could you do something like:

  mkfifo db1
  psql -h "db1" -t -q -c "$query" > db1
  mkfifo db2
  psql -h "db2" -t -q -c "$query" > db2
  diff -u -0 db1 db2

That should work with most shells under Unix. . .

Have fun,
  Sam

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Table definition

[Sam Mason]

Bruno Prévost wrote:
>Anybody know how to obtain the table definition in text.

Not quite sure if this is quite what you're after, but would:

  $ pg_dump -st foo

help at all?  It gives out the SQL that you would need to enter to
re-create the table.


  Sam

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Problems with HAVING

[Sam Mason]

Kaloyan Iliev Iliev wrote:
>select test.name
>from test
>where test.name = foo.name
>having max(test.date)

I don't think you use the "having" clause like you've done there.  I
think you want to be doing something more like:

  select test.name
  from test
  where test.name = foo.name
and test.date in (select max(date) from test);

But I may have misinterpreted you. . .

Cheers,
  Sam

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Syntax error while altering col-type

[Sam Mason]

KÖPFERL Robert wrote:
>I'm a little bit perplexed now... is it really the case that pre 8.0 systems
>aren't able to change col-types?

I would guess that the column type altering code is just short hand
for creating a new column of the correct type, copying the old column
into the new one, deleting the old column, and renaming the new column
to be the same as the old one.

That is just a guess though!  But if it's all bundled inside a
transaction it should have about the same semantics, shouldn't it?

Cheers,
  Sam

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])