Perl Query Problem

am 27.04.2004 05:10:47 von cslyon

OK, here is the layout.

I have 10+ tables with some of the same information in each in table of =
a
mysql database. Here is an example:

=20
Table 1
name count
---------+-------
Widgets-A| 10
Widgets-B| 5
Widgets-C| 10
Widgets-D| 5
Widgets-E| 15

Table 2
name count
---------+-------
Widgets-A| 15
Widgets-B| 5
Widgets-C| 10

=A0
Table 3
name count
---------+-------
Widgets-D| 5
Widgets-E| 15 =20

I need to take the names and add the counts up for between each table =
and I
am not sure how to make that happen. I would be able to do a select =
query so
that the values are all in one query, like this:

name count
---------+-------
Widgets-A| 10
Widgets-B| 5
Widgets-C| 10
Widgets-D| 5
Widgets-E| 15
Widgets-A| 15
Widgets-B| 5
Widgets-C| 10
Widgets-D| 5
Widgets-E| 15 =20

So, how to I get the values to look like in variable so that I can push =
them
back into a new table:

New Table
name count
---------+-------
Widgets-A| 25
Widgets-B| 10
Widgets-C| 20
Widgets-D| 10
Widgets-E| 25

Any ideas?

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Perl Query Problem

am 27.04.2004 19:03:29 von MTA-Traffic

iH

not sure how to do this with only one query but since mention Perl; you=20=

could

query each table and put the results into a hash with the key being the=20=

"name" field and the hash value being the "count". once you have=20
queried each table your hash should represent the new table you=20
described

- hcir

mysql

On Apr 26, 2004, at 7:10 PM, wrote:

> OK, here is the layout.
>
> I have 10+ tables with some of the same information in each in table=20=

> of a
> mysql database. Here is an example:
>
>
> Table 1
> name count
> ---------+-------
> Widgets-A| 10
> Widgets-B| 5
> Widgets-C| 10
> Widgets-D| 5
> Widgets-E| 15
>
>
> Table 2
> name count
> ---------+-------
> Widgets-A| 15
> Widgets-B| 5
> Widgets-C| 10
>
> =A0
> =A0
> Table 3
> name count
> ---------+-------
> Widgets-D| 5
> Widgets-E| 15
>
>
>
>
> I need to take the names and add the counts up for between each table=20=

> and I
> am not sure how to make that happen. I would be able to do a select=20
> query so
> that the values are all in one query, like this:
>
>
> name count
> ---------+-------
> Widgets-A| 10
> Widgets-B| 5
> Widgets-C| 10
> Widgets-D| 5
> Widgets-E| 15
> Widgets-A| 15
> Widgets-B| 5
> Widgets-C| 10
> Widgets-D| 5
> Widgets-E| 15
>
> So, how to I get the values to look like in variable so that I can=20
> push them
> back into a new table:
>
> New Table
> name count
> ---------+-------
> Widgets-A| 25
> Widgets-B| 10
> Widgets-C| 20
> Widgets-D| 10
> Widgets-E| 25
>
> Any ideas?
>
>
> --=20
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: =
http://lists.mysql.com/perl?unsub=3Dtraf@mtaonline.net
>
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Perl Query Problem

am 27.04.2004 19:03:29 von MTA-Traffic

Re: Perl Query Problem

am 27.04.2004 20:43:31 von Rudy Lippan

On Mon, 26 Apr 2004 cslyon@qxzi.net wrote:

> I have 10+ tables with some of the same information in each in table of a
> mysql database. Here is an example:
>
> Any ideas?
>
>
>

Something like this:

SELECT name, sum(value) as total from (SELECT * from test1 UNION ALL SELECT *
FROM test2) AS d_table group by name;

Rudy

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Perl Query Problem

am 27.04.2004 20:43:31 von Rudy Lippan

Re: Perl Query Problem

am 27.04.2004 22:42:03 von Christopher Pryce

On Apr 27, 2004, at 1:43 PM, Rudy Lippan wrote:

>> I have 10+ tables with some of the same information in each in table
>> of a
>> mysql database. Here is an example:
>>
>> Any ideas?
>>
>>
>>
>
> Something like this:
>
> SELECT name, sum(value) as total from (SELECT * from test1 UNION ALL
> SELECT *
> FROM test2) AS d_table group by name;
>

Cool. Which version of MySQL supports sub-selects?

With version 4.0.13 the above code tosses an error. I had to create a
temporary table and Insert Select the union into it.

And the Perl code would then be something along the lines of :
# untested for syntax errors. error checking omitted...
#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect('dbi:mysql:test;host=localhost', '', '',
{RaiseError=>1});
$dbh->do( qq( Drop Table If Exists results ));

# assuming you don't have any other types of tables in this database
my @list_of_tables = $dbh->tables();

$dbh->do( qq( Create Table results ( name varchar( 10 ), qty Int ) ) );

my $sql = 'Insert Into results ' . join(' Union All ', map{ "Select *
from $_" } @list_of_tables );
$dbh->do ( $sql );

my $results = $dbh->selectall_arrayref( qq(Select name, Sum(qty) from
results Group By name));
print "| " . join("\t| ", @$_ ) . " | \n" for ( @$results );

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Perl Query Problem

am 27.04.2004 22:42:03 von Christopher Pryce

Re: Perl Query Problem

am 27.04.2004 23:19:47 von Rudy Lippan

On Tue, 27 Apr 2004, cp wrote:

> On Apr 27, 2004, at 1:43 PM, Rudy Lippan wrote:
>
> >> I have 10+ tables with some of the same information in each in table
> >> of a
> >> mysql database. Here is an example:
> >>
> >> Any ideas?
> >>
> >>
> >>
> >
> > Something like this:
> >
> > SELECT name, sum(value) as total from (SELECT * from test1 UNION ALL
> > SELECT *
> > FROM test2) AS d_table group by name;
> >
>
> Cool. Which version of MySQL supports sub-selects?
>

4.1.x+

> With version 4.0.13 the above code tosses an error. I had to create a
> temporary table and Insert Select the union into it.
>

That is Probably the best way in 4.0.

> And the Perl code would then be something along the lines of :
> # untested for syntax errors. error checking omitted...
> #!/usr/bin/perl
>
> use DBI;
>
> my $dbh = DBI->connect('dbi:mysql:test;host=localhost', '', '',
> {RaiseError=>1});
> $dbh->do( qq( Drop Table If Exists results ));
>
> # assuming you don't have any other types of tables in this database
> my @list_of_tables = $dbh->tables();
>
> $dbh->do( qq( Create Table results ( name varchar( 10 ), qty Int ) ) );
>
> my $sql = 'Insert Into results ' . join(' Union All ', map{ "Select *
> from $_" } @list_of_tables );
> $dbh->do ( $sql );
>

Or Combined into one statement:
create temporary table t1 (SELECT * from test1)UNION ALL (SELECT * from test2);

> my $results = $dbh->selectall_arrayref( qq(Select name, Sum(qty) from
> results Group By name));
> print "| " . join("\t| ", @$_ ) . " | \n" for ( @$results );

Rudy

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Perl Query Problem

am 27.04.2004 23:19:47 von Rudy Lippan