SQL TBL QRY WTF ?!?
revjack
in the head to get it started again???
I just want a query to tell me how many records are in two tables..
select count(*) from table1;
COUNT(*)
----------
244
select count(*) from table2;
COUNT(*)
----------
7390
OK FINE, but to get them all in one query, this is all my
brane can come up with:
select count(table1.colname), count(table2.colname)
from table1, table2;
BUT this yields wacky huge numbers that are wrong! I am
being very stupid, will someone please unstupidify me??!?
GROUP, maybe? aieieieieeeee
--
___________________
rev...@revjack.net
Ben Wolfson
> 7390
>
>OK FINE, but to get them all in one query, this is all my
>brane can come up with:
>
> select count(table1.colname), count(table2.colname)
> from table1, table2;
>
>BUT this yields wacky huge numbers that are wrong! I am
>being very stupid, will someone please unstupidify me??!?
I are not a database guy, but doesn't the above do a cartesian product
deal?
--
BTR
The Glass Marble, mistaking the No. 37 Penpoint for the Four-Holed
Button, pushed it into the Yawning Chasm.
revjack
: In article <bd9tmc$t35$2...@news1.radix.net>, revjack wrote:
:> 7390
:>
:>OK FINE, but to get them all in one query, this is all my
:>brane can come up with:
:>
:> select count(table1.colname), count(table2.colname)
:> from table1, table2;
:>
:>BUT this yields wacky huge numbers that are wrong! I am
:>being very stupid, will someone please unstupidify me??!?
: I are not a database guy, but doesn't the above do a cartesian product
: deal?
Apparently.
--
___________________
rev...@revjack.net
revjack
select * from
(select count(*) "ONE" from table1),
(select count(*) "TWO" from table2);
Jeebus.
No idea if that's efficient though.
I would still like for someone to kick me in the head please.
--
___________________
rev...@revjack.net
Jeremy Impson
jdimpson@monster$ psql mobiledb
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
mobiledb=# select count(bike_log.elapsed);
count
-------
80
(1 row)
mobiledb=# select count(runners_log.duration);
count
-------
110
(1 row)
mobiledb=# select count(runners_log.duration), count(bike_log.elapsed);
count | count
-------+-------
9020 | 8800
(1 row)
I have no idea. But it's consistent, at least. The second count looks
like the two counts multiplied together.
--Jeremy
--
Jeremy Impson
jdim...@acm.org
http://impson.tzo.com/~jdimpson
Mark Hill
> Ah, this works:
>
> select * from
> (select count(*) "ONE" from table1),
> (select count(*) "TWO" from table2);
>
> Jeebus.
>
> No idea if that's efficient though.
it's probably not too bad.
Hmm, in pgsql, this works nicely:
select (select count(*) from table1) + (select count(*) from table2);
actually, yeah, I got the same thing to work in oracle too, if you add
"from dual" to the end.
Beable van Polasm
>
> On 24 Jun 2003, revjack wrote:
>
> > AAAGGH my btain is not working today, someone please kick me
> > in the head to get it started again???
> >
> > I just want a query to tell me how many records are in two tables..
> >
> > from table1, table2;
> >
> > BUT this yields wacky huge numbers that are wrong! I am
> > being very stupid, will someone please unstupidify me??!?
>
>
> like the two counts multiplied together.
Hey, you could muck around with SQueaL for ever, which will make you
SQueaL like a PiG, or you could do the sensible thing and use Perl:
#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;
my $data_source = "dbi:Pg:dbname=pgdb";
my $username = "";
my $auth = "";
my $dbh = DBI->connect($data_source, $username, $auth,
{ RaiseError => 1, AutoCommit => 0 });
my $test1count = $dbh->prepare("select count(test1.id);");
my $test2count = $dbh->prepare("select count(test2.id);");
$test1count->execute;
$test2count->execute;
my @row_ary1 = $test1count->fetchrow_array;
my @row_ary2 = $test2count->fetchrow_array;
my $total = $row_ary1[0] + $row_ary2[0];
print("total = $total rows\n");
$test1count->finish;
$test2count->finish;
$dbh->disconnect;
__END__
--
You make so many mistakes I almost believe you are doing it on purpose!
-- Herman Trivilino
http://beable.com
revjack
: Jeremy Impson <jdim...@acm.org> writes:
:>
:> On 24 Jun 2003, revjack wrote:
:>
:> > AAAGGH my btain is not working today, someone please kick me
:> > in the head to get it started again???
:> >
:> > I just want a query to tell me how many records are in two tables..
:> >
:> > select count(table1.colname), count(table2.colname)
:> > from table1, table2;
:> >
:> > BUT this yields wacky huge numbers that are wrong! I am
:> > being very stupid, will someone please unstupidify me??!?
:>
:>
:> I have no idea. But it's consistent, at least. The second count looks
:> like the two counts multiplied together.
: Hey, you could muck around with SQueaL for ever, which will make you
: SQueaL like a PiG, or you could do the sensible thing and use Perl:
Yah, I'm talking to Oracle thru DBI. I just wanted to
execute one statement instead of two. I knew there was a way
to to it, I just couldn't remember it (eventually I did).
There's probably some Oracle administrative table that has
the record count of each table, but I wouldn't begin to know
where to look. Last time I checked, there's like a thousand
of those admin tables. Oracle is weird.
--
___________________
rev...@revjack.net
Hong Ooi
<beable+...@beable.com.invalid> wrote:
Couldn't you use SAS?
data table / view=table;
set table1 table2;
run;
proc summary data=table print;
run;
--
Hong Ooi | "There is no Bondi Beach
ho...@zipworld.com.au | in Nova Scotia."
http://www.zip.com.au/~hong | -- BBC
Sydney, Australia |
Andrew Pearson
>
> In alt.religion.kibology Beable van Polasm <beable+...@beable.com.invalid> wrote:
> : Jeremy Impson <jdim...@acm.org> writes:
> :>
> :> On 24 Jun 2003, revjack wrote:
> :>
> :> > AAAGGH my btain is not working today, someone please kick me
> :> > in the head to get it started again???
> :> >
> :> > I just want a query to tell me how many records are in two tables..
> :> >
> :> > select count(table1.colname), count(table2.colname)
> :> > from table1, table2;
> :> >
> :> > BUT this yields wacky huge numbers that are wrong! I am
> :> > being very stupid, will someone please unstupidify me??!?
> :>
> :>
> :> I have no idea. But it's consistent, at least. The second count looks
> :> like the two counts multiplied together.
>
> : Hey, you could muck around with SQueaL for ever, which will make you
> : SQueaL like a PiG, or you could do the sensible thing and use Perl:
No! Bad Beagle! No Biscuit! You doughnut use a procedural language and
write a program, you find out how to do it properly, that's how you
become revered, like that nice Revered Jack did. Also, and I don't care
if you are TROLLIJNG, it's not SQueaL, it's SQuirreL and the nice
SQuirreL goes and gets the dayter out of the dayter base for you like
Dr. E. Haddockk trained it to. The SQuirrel does NOT anally rape you, it
showers the benefits of dayter upon you.
> Yah, I'm talking to Oracle thru DBI. I just wanted to
> execute one statement instead of two. I knew there was a way
> to to it, I just couldn't remember it (eventually I did).
>
> There's probably some Oracle administrative table that has
> the record count of each table, but I wouldn't begin to know
> where to look. Last time I checked, there's like a thousand
> of those admin tables. Oracle is weird.
>
Hmmnm. Right, and right. If you were using a pure and holy dayterbase,
like Infermicks you would find the number of rows in systables.nrows
WHERE tabid = name_of_your_table - but you wouldn't rely on it as it is
only there for the optimiser to use, and is generally only correct
immediately after an UPDATE STATISTICS. Fortunulately, you have done it
the pure, good and noble way, and given the SQuirrel some exercise.
Dunno about Oracle though.
Also, will nobody hint the optimiser?
[pt.lu does not recognise alt.spank.beable, which seems fair enough as
it also rejects alt.fan.beable]
--
"STOP BLACKHOLING PLONKTON! PLONKS MUST BE REORBITED TO FORM A
BORNAGAIN GALAXYNET" Dave DeLaney, somewhere, somewhen.
Fantod
> In alt.religion.kibology Beable van Polasm
> <beable+...@beable.com.invalid> wrote:
>: Jeremy Impson <jdim...@acm.org> writes:
>:>
>:> On 24 Jun 2003, revjack wrote:
>:>
>:> > AAAGGH my btain is not working today, someone please kick me
>:> > in the head to get it started again???
>:> >
>:> > I just want a query to tell me how many records are in two tables..
>:> >
>:> > select count(table1.colname), count(table2.colname)
>:> > from table1, table2;
>:> >
>:> > BUT this yields wacky huge numbers that are wrong! I am
>:> > being very stupid, will someone please unstupidify me??!?
You are counting the cross product, which is what you get when you have "from
table1, table2".
>:> I have no idea. But it's consistent, at least. The second count
>:> looks like the two counts multiplied together.
They both should be, so shows what I know. Less, I could see, but more than
the cross product?
>: Hey, you could muck around with SQueaL for ever, which will make you
>: SQueaL like a PiG, or you could do the sensible thing and use Perl:
Not providing one line response! #! /usr/bin/PLONK!
--
Patrick Phelan
w____\\W//___w Te Hupenui
This line won't appear in the sig!
kerri
news:eehe6ey...@dingo.beable.com:
> you could do the sensible thing and use Perl
Or! Even better!
<makes two fists and begins counting on fingers>
One, two, three...
http://www.techtorial.com/today/old/advice.jpg
--kerri
Fantod
> revjack <rev...@revjack.net> writes:
>> select * from
>> (select count(*) "ONE" from table1),
>> (select count(*) "TWO" from table2);
vs.
> select (select count(*) from table1) + (select count(*) from table2);
If I may add another two cents, [Mark]'s version is more efficient, because
the "from x, y" will attempt to calculate the cross product from those two
tables. You get the results you are expecting, but only because those are one
element tables. When returning a count, the SQL engine should know that it is
one element, and should not mess around with joins and temporary tables. This
makes the "select (select ... from inner where inner.id = outer.id),
outer.xyz from outer" as efficient, if not more so, than table joins in a lot
of situations. (At least with databases that will let you get away with
that.)
Also, I personally would prefer to see something like
Select
(Select count(*) from table1) AS CountOfTable1,
(Select count(*) from table2) AS CountOfTable2;
Better documentation.
> actually, yeah, I got the same thing to work in oracle too, if you add
> "from dual" to the end.
Odd, though I haven't used actually Oracle in ages.
--
Patrick Phelan
w____\\W//___w Te Hupenui
If you look deeply into the jello you will see him,
but by then it will be much too late.
Jeremy Impson
> In alt.religion.kibology Beable van Polasm <beable+...@beable.com.invalid> wrote:
> : Jeremy Impson <jdim...@acm.org> writes:
> :>
> :> On 24 Jun 2003, revjack wrote:
> :>
> :> > AAAGGH my btain is not working today, someone please kick me
> :> > in the head to get it started again???
> :> >
> :> > I just want a query to tell me how many records are in two tables..
> :> >
> :> > select count(table1.colname), count(table2.colname)
> :> > from table1, table2;
> :> >
> :> > BUT this yields wacky huge numbers that are wrong! I am
> :> > being very stupid, will someone please unstupidify me??!?
> :>
> :>
> :> I have no idea. But it's consistent, at least. The second count looks
> :> like the two counts multiplied together.
>
> : Hey, you could muck around with SQueaL for ever, which will make you
> : SQueaL like a PiG, or you could do the sensible thing and use Perl:
>
> Yah, I'm talking to Oracle thru DBI. I just wanted to
> execute one statement instead of two. I knew there was a way
> to to it, I just couldn't remember it (eventually I did).
But Beable's trying to tempt you into joining him in a life of SIN.
ALL COMPUTATION MUST OCCUR ON THE DATABASE. That's what it's fucking
there for. Stored procedures and all that. Consistency. Stupid client
code that don't do more than display. I HAVE SPOKEN.
> There's probably some Oracle administrative table that has
> the record count of each table, but I wouldn't begin to know
> where to look. Last time I checked, there's like a thousand
> of those admin tables. Oracle is weird.
AND NOT PORTABLE.
Jeremy Impson
> Hey, you could muck around with SQueaL for ever, which will make you
> SQueaL like a PiG, or you could do the sensible thing and use Perl:
Dunno about RJ, but I AM.
>
> #!/usr/bin/perl -w
>
> use strict;
> use warnings;
> use DBI;
>
> my $data_source = "dbi:Pg:dbname=pgdb";
> my $username = "";
> my $auth = "";
> my $dbh = DBI->connect($data_source, $username, $auth,
> { RaiseError => 1, AutoCommit => 0 });
>
> my $test1count = $dbh->prepare("select count(test1.id);");
> my $test2count = $dbh->prepare("select count(test2.id);");
HELLO THIS IS SQueaL SO YOU ARE AS UNCLEAN AS US.
>
> $test1count->execute;
> $test2count->execute;
>
> my @row_ary1 = $test1count->fetchrow_array;
> my @row_ary2 = $test2count->fetchrow_array;
>
> my $total = $row_ary1[0] + $row_ary2[0];
ACTUALLY YOU ARE UNCLEANER, er, MORE UNCLEAN. All computation must happen
on the database. Clients are only for the presentation/transport.
> print("total = $total rows\n");
>
> $test1count->finish;
> $test2count->finish;
> $dbh->disconnect;
>
> __END__
My Perl database code is better than yours. QED
Jeremy Impson
Yes, we know your cow-orker is feeble-minded. I don't think it's very
nice of you to continually point out his/her inadequacies.
Mark Hill
> ACTUALLY YOU ARE UNCLEANER, er, MORE UNCLEAN. All computation must happen
> on the database. Clients are only for the presentation/transport.
Yes! All computation must occur on the database, and all communication with
the database must be done with something like SQL*Net!
http://www.google.com/search?hl=en&lr=&ie=ISO-8859-1&q=%22mark+hill%22+oracle+sql.net
That being said, I use pgsql these days whenever possible.
revjack
: ALL COMPUTATION MUST OCCUR ON THE DATABASE. That's what it's fucking
: there for. Stored procedures and all that. Consistency. Stupid client
: code that don't do more than display.
This is, in fact, the Party Line from the Oracle wags that I
have taken too many classes from, comrade. The gears of the
state are greased with the boold of the client interpreters.
: I HAVE SPOKEN.
Have a banana.
: AND NOT PORTABLE.
I remember writing for portability....when you have a client
that says, "It HAS TO RUN on AIX v0.9 for our Arctic Circle
DEW installation", you COULD still make it portable, you
could. But would you?
--
___________________
rev...@revjack.net
revjack
: On 25 Jun 2003, Beable van Polasm wrote:
:> Hey, you could muck around with SQueaL for ever, which will make you
:> SQueaL like a PiG, or you could do the sensible thing and use Perl:
: Dunno about RJ, but I AM.
:> my $test1count = $dbh->prepare("select count(test1.id);");
:> my $test2count = $dbh->prepare("select count(test2.id);");
: HELLO THIS IS SQueaL SO YOU ARE AS UNCLEAN AS US.
Don't be meen to Beable just because he posted untested perl
to USETENT that errors out. Some people still need to use
perl 4. Not that there's anything wrong with that!
--
___________________
rev...@revjack.net
Beable van Polasm
That's the second-worst picture of a camel I've seen this week!
--
That is Australia! We love to PUNCH! EVERY! ONE! WE! MEET!
-- H.G. Nelson
http://beable.com
Jeremy Impson
> That being said, I use pgsql these days whenever possible.
I AGREE WITH THIS PSOT.
Jeremy Impson
ATTN: DON'T READ THIS. THIS IS A PRIVATE, GEEKY CONVERSATION B/W ME A
REVJACK. MARK HILL MAY READ IT IF HE WANTS TO. BEABLE MAY NOT.
On 26 Jun 2003, revjack wrote:
> Jeremy Impson <jdim...@acm.org> wrote:
>
> : ALL COMPUTATION MUST OCCUR ON THE DATABASE. That's what it's fucking
> : there for. Stored procedures and all that. Consistency. Stupid client
> : code that don't do more than display.
>
> This is, in fact, the Party Line from the Oracle wags that I
> have taken too many classes from, comrade. The gears of the
> state are greased with the boold of the client interpreters.
It makes sense when you know that other users of the database aren't as
smart as we are. And usually they are not. Acuase otherwise you get LOTs
of bugs.
> : I HAVE SPOKEN.
>
> Have a banana.
Thanks.
> : AND NOT PORTABLE.
>
> I remember writing for portability....when you have a client
> that says, "It HAS TO RUN on AIX v0.9 for our Arctic Circle
> DEW installation", you COULD still make it portable, you
> could. But would you?
I don't write ANY code that isn't portable. Which is why I'm not a
programmer.
Beable van Polasm
>
> In alt.religion.kibology Jeremy Impson <jdim...@acm.org> wrote:
> : On 25 Jun 2003, Beable van Polasm wrote:
>
> :> Hey, you could muck around with SQueaL for ever, which will make you
> :> SQueaL like a PiG, or you could do the sensible thing and use Perl:
>
> : Dunno about RJ, but I AM.
Me too!
> :> my $test1count = $dbh->prepare("select count(test1.id);");
> :> my $test2count = $dbh->prepare("select count(test2.id);");
>
> : HELLO THIS IS SQueaL SO YOU ARE AS UNCLEAN AS US.
I never said I was writing something "clean", I just inferred
that I was SICK OF MUCKING AROUND WITH SQueaL!
> Don't be meen to Beable just because he posted untested perl
> to USETENT that errors out.
Pfffft! The only errors that code would get would be if you
didn't change the $data_source, or if you didn't have Postgres
installed, or if the user you ran it as didn't have persimmons
to use the database! Apart from that, TOTALLY ERROR-FREE!
THE CHANCE TO PROVE YOU WRONG *AGAIN* WAS TOO MUCH TO RESIST!
SPANK ME! SPANK ME!
> Some people still need to use perl 4. Not that there's anything
> wrong with that!
Yes there is - no chomp. I WANT A CHOMP! BAYBEE!
--
The chance to prove you wrong AGAIN was too much to resist.
-- Disinformation Agent 2740164
http://beable.com
Jøhnny Fävòrítê (it means "genetic antagonism")
> kerri wrote:
>> http://www.techtorial.com/today/old/advice.jpg
>
> Yes, we know your cow-orker is feeble-minded. I don't think it's very
> nice of you to continually point out his/her inadequacies.
Why do you people keep defending those chicken-scratchings masquerading
as a programming language? On the one hand you can get all indignant
about adding up record counts in client code but on the other you can
defend a language that looks like an explosion at the ASCII factory?
You kids these days.
Joe Manfre
>don't be meen to beable just because he posted untested perl to
>usetent that errors out.
Does this mean Beable is, EQUALLY, Matt Wright? Have we discovered
his sekrit at last?
Oh no, wait, Beable used that __END__ statement in his Perl script.
Never mind.
>some people still need to use perl 4. not that there's anything wrong
>with that!
Perl 4 has got ITS OWN CONVERTIBLE and a HEADBAAAAAAAAAAAAAND! What
more could it need?
JM
(See song list at http://www.aspma.com/comps/bigwood.htm )
Ben Wolfson
Fävòrítê (it means "genetic antagonism") wrote:
>Why do you people keep defending those chicken-scratchings masquerading
>as a programming language? On the one hand you can get all indignant
>about adding up record counts in client code but on the other you can
>defend a language that looks like an explosion at the ASCII factory?
>
>You kids these days.
SSC: Two days ago I wrote something in Perl, and I may have to do it
again. My boss doesn't know what Python is.
Jøhnny Fävòrítê (it means "genetic antagonism")
> SSC: Two days ago I wrote something in Perl, and I may have to do it
> again. My boss doesn't know what Python is.
I've read just enough about Python to think it might be cool. I know
one or two people who are really passionate about it, and can make a
good case. Alas, I'm a little too old-sk00l to get past the whole
not-a-compiled-language thing. Yes, I know premature optimization is
the root of all evil, and choosing a language because it's more
efficient with CPU time as opposed to programmer time is arguably the
worst premature optimization of all. This line of thinking somehow
fails to sway the not-a-compiled-language part of my brain, so I just
keep getting a little bit better at C++ every year, to the point where I
will soon be indistinguishable from Bjarne Stroustrup. Thinning hair:
check ...
Then there's the Lisp-based languages. The only Lisp proponent I've
read at any length is Paul Graham, and I think he crossed the line from
"proponent" to "drank the kool-aid" quite some time ago. In his mind,
you either program in Lisp or you're stupid, full stop. Still, I think
his zealotry might be masking some good concepts, if I were to take the
time to figure them out.
But Perl, geez, that does nothing but make me laugh every time I
accidentally get close enough to get a whiff of it.
Ricky Morse
Jøhnny Fävòrítê (it means "genetic antagonism") <thi...@fake.com>
wrote:
> Why do you people keep defending those chicken-scratchings masquerading
> as a programming language? On the one hand you can get all indignant
> about adding up record counts in client code but on the other you can
> defend a language that looks like an explosion at the ASCII factory?
Who's talking about befunge?
Ricky