surprised by query results

#1

Hello, this surprised me:
select dr_cat,sum(dr_c1_amt) from r_amount
group by dr_cat;
dr_ca t | sum
1 | 175.00
2 | 30.00
(2 rows)

next add another table
select dr_cat,sum(dr_c1_amt) from r_amount
where categories.ca_jud_apply='Y'
group by dr_cat;
dr_cat | sum
1 | 4200.00
2 | 720.00
(2 rows)
24 categories rows where ca_jud_apply='Y'
175 * 24 = 4200 30 * 24 = 720

No question. Just FYI
Paul

#2

"PaulS" <semenick (AT) ix (DOT) netcom.com> schreef in bericht
news:13hi6pisf90ar8c (AT) corp (DOT) supernews.com...

Quote:

Hello, this surprised me:
select dr_cat,sum(dr_c1_amt) from r_amount
group by dr_cat;
dr_ca t | sum
1 | 175.00
2 | 30.00
(2 rows)

next add another table
select dr_cat,sum(dr_c1_amt) from r_amount
where categories.ca_jud_apply='Y'
group by dr_cat;
dr_cat | sum
1 | 4200.00
2 | 720.00
(2 rows)
24 categories rows where ca_jud_apply='Y'
175 * 24 = 4200 30 * 24 = 720

No question. Just FYI
Cannot join you in being suprised about an unexpected result when using an

implied and uncontrolled join.

HansH

#3

PaulS <semenick (AT) ix (DOT) netcom.com> wrote:

Quote:

Hello, this surprised me:
select dr_cat,sum(dr_c1_amt) from r_amount
group by dr_cat;
dr_ca t | sum
1 | 175.00
2 | 30.00
(2 rows)

next add another table
select dr_cat,sum(dr_c1_amt) from r_amount
where categories.ca_jud_apply='Y'
group by dr_cat;
dr_cat | sum
1 | 4200.00
2 | 720.00
(2 rows)
24 categories rows where ca_jud_apply='Y'
175 * 24 = 4200 30 * 24 = 720

You're right, that's surprising.
I would have expected the following result from the second query:

ERROR: missing FROM-clause entry for table "categories"

What version is this? How is the definition of r_amount?

Yours,
Laurenz Albe

#4

"Laurenz Albe" <invite (AT) spam (DOT) to.invalid> wrote

Quote:

PaulS <semenick (AT) ix (DOT) netcom.com> wrote:
Hello, this surprised me:
select dr_cat,sum(dr_c1_amt) from r_amount
group by dr_cat;
dr_ca t | sum
1 | 175.00
2 | 30.00
(2 rows)

next add another table
select dr_cat,sum(dr_c1_amt) from r_amount
where categories.ca_jud_apply='Y'
group by dr_cat;
dr_cat | sum
1 | 4200.00
2 | 720.00
(2 rows)
24 categories rows where ca_jud_apply='Y'
175 * 24 = 4200 30 * 24 = 720

You're right, that's surprising.
I would have expected the following result from the second query:

ERROR: missing FROM-clause entry for table "categories"

What version is this? How is the definition of r_amount?

Yours,
Laurenz Albe

#5

begin PaulS schrieb:

Quote:

"Laurenz Albe" <invite (AT) spam (DOT) to.invalid> wrote in message
select dr_cat,sum(dr_c1_amt) from r_amount
where categories.ca_jud_apply='Y'
group by dr_cat;
dr_cat | sum
1 | 4200.00
2 | 720.00

You're right, that's surprising.
I would have expected the following result from the second query:

ERROR: missing FROM-clause entry for table "categories"

What version is this? How is the definition of r_amount?

Yours,
Laurenz Albe

here's what I have on version 7.3.3

I think, you havn't understand Laurenz. Your SQL isn't correct, you
get syntax errors because your table 'categories' are not in the FROM -
list. At least in newer versions...

(i know, not with 7.3)

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

surprised by query results

comp.databases.postgresql comp.databases.postgresql

surprised by query results - 10-19-2007 , 04:00 PM

Re: surprised by query results - 10-20-2007 , 12:34 PM

Re: surprised by query results - 10-22-2007 , 01:50 AM

Re: surprised by query results - 10-22-2007 , 07:36 AM

Re: surprised by query results - 10-22-2007 , 07:48 AM