Grokbase › Groups › Perl › dbi-users › August 2001
FAQ
Hello,

I am not a db programmer so please excuse this probably simple question but
I can't seem to figure it out.

There is an Access 2000 database and inside this database there is a table
called Customers.

Customers has various columns and three of those are called Company,
ThisMonth and Carrier

The ThisMonth column contains how much bandwidth a particular company pushed
and Carrier specifies
which Telco they connect to.

I am trying to write a SQL Query that gives me the customer who pushed the
most bandwidth for a particular
Carrier and also how much bandwidth that was.

I have this so far:

$total_all = $dbh->prepare (qq {SELECT MAX(ThisMonth) FROM Customers Where
Carrier = 'Qwest'});

That works but it just gives me the amount of bandwidth pushed and not the
customer who pushed it.

I have tried all manner of other queries to get BOTH the customer in
particular and the MAX(ThisMonth) but they don't work.
I get syntax errors being reported back thru the DBI.

For instance I tried things like:

$total_all = $dbh->prepare (qq {SELECT Company, MAX(ThisMonth) FROM
Customers Where Carrier = 'Qwest'});

and got this back:

ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] You tried
to execute a query that does not include the
specified expression 'Company' as part of an aggregate function.
(SQL-37000)(DBD: st_execute/SQLExecute err=-1) at D:\BotArchive
\Bandwidth\Summary\test.pl line 117, <STDIN> line 1.
Any ideas?

Thanks,

John

Search Discussions

  • Jeff Eckermann at Aug 23, 2001 at 6:27 pm ⇧
    You need to add "GROUP BY Company" to the end of your SQL statement.
    I find that easy to forget, but the now-familiar error messages help!
    ----- Original Message -----
    From: "Isaac" <isaac@netos.com>
    To: <dbi-users@perl.org>
    Sent: Thursday, August 23, 2001 12:52 PM
    Subject: SQL Query Question

    Hello,

    I am not a db programmer so please excuse this probably simple question but
    I can't seem to figure it out.

    There is an Access 2000 database and inside this database there is a table
    called Customers.

    Customers has various columns and three of those are called Company,
    ThisMonth and Carrier

    The ThisMonth column contains how much bandwidth a particular company pushed
    and Carrier specifies
    which Telco they connect to.

    I am trying to write a SQL Query that gives me the customer who pushed the
    most bandwidth for a particular
    Carrier and also how much bandwidth that was.

    I have this so far:

    $total_all = $dbh->prepare (qq {SELECT MAX(ThisMonth) FROM Customers Where
    Carrier = 'Qwest'});

    That works but it just gives me the amount of bandwidth pushed and not the
    customer who pushed it.

    I have tried all manner of other queries to get BOTH the customer in
    particular and the MAX(ThisMonth) but they don't work.
    I get syntax errors being reported back thru the DBI.

    For instance I tried things like:

    $total_all = $dbh->prepare (qq {SELECT Company, MAX(ThisMonth) FROM
    Customers Where Carrier = 'Qwest'});

    and got this back:

    ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] You tried
    to execute a query that does not include the
    specified expression 'Company' as part of an aggregate function.
    (SQL-37000)(DBD: st_execute/SQLExecute err=-1) at D:\BotArchive
    \Bandwidth\Summary\test.pl line 117, <STDIN> line 1.
    Any ideas?

    Thanks,

    John




  • Mark Sheinbaum at Aug 23, 2001 at 6:36 pm ⇧
    select company,carrier from customers
    where this_month = (select max(this_month) from customers)
    and carrier = 'QWEST'

    -----Original Message-----
    From: Jeff Eckermann
    Sent: Thursday, August 23, 2001 11:28 AM
    To: Isaac; dbi-users@perl.org
    Subject: Re: SQL Query Question


    You need to add "GROUP BY Company" to the end of your SQL statement.
    I find that easy to forget, but the now-familiar error messages help!
    ----- Original Message -----
    From: "Isaac" <isaac@netos.com>
    To: <dbi-users@perl.org>
    Sent: Thursday, August 23, 2001 12:52 PM
    Subject: SQL Query Question

    Hello,

    I am not a db programmer so please excuse this probably simple question but
    I can't seem to figure it out.

    There is an Access 2000 database and inside this database there is a table
    called Customers.

    Customers has various columns and three of those are called Company,
    ThisMonth and Carrier

    The ThisMonth column contains how much bandwidth a particular company pushed
    and Carrier specifies
    which Telco they connect to.

    I am trying to write a SQL Query that gives me the customer who pushed the
    most bandwidth for a particular
    Carrier and also how much bandwidth that was.

    I have this so far:

    $total_all = $dbh->prepare (qq {SELECT MAX(ThisMonth) FROM Customers Where
    Carrier = 'Qwest'});

    That works but it just gives me the amount of bandwidth pushed and not the
    customer who pushed it.

    I have tried all manner of other queries to get BOTH the customer in
    particular and the MAX(ThisMonth) but they don't work.
    I get syntax errors being reported back thru the DBI.

    For instance I tried things like:

    $total_all = $dbh->prepare (qq {SELECT Company, MAX(ThisMonth) FROM
    Customers Where Carrier = 'Qwest'});

    and got this back:

    ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] You tried
    to execute a query that does not include the
    specified expression 'Company' as part of an aggregate function.
    (SQL-37000)(DBD: st_execute/SQLExecute err=-1) at D:\BotArchive
    \Bandwidth\Summary\test.pl line 117, <STDIN> line 1.
    Any ideas?

    Thanks,

    John




  • Wsheldah at Aug 24, 2001 at 1:38 pm ⇧
    Uh, I believe that version will return no records if a customer with another
    carrier used more bandwidth than the highest bandwidth QWEST customer. I would
    restrict both main AND subquery to QWEST, to get the most bandwidth used by a
    QWEST customer.



    Mark Sheinbaum <msheinbaum%excitehome.net@interlock.lexmark.com> on 08/23/2001
    02:34:06 PM

    To: "'dbi-users@perl.org'" <dbi-users%perl.org@interlock.lexmark.com>
    cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
    Subject: RE: SQL Query Question


    select company,carrier from customers
    where this_month = (select max(this_month) from customers)
    and carrier = 'QWEST'

    -----Original Message-----
    From: Jeff Eckermann
    Sent: Thursday, August 23, 2001 11:28 AM
    To: Isaac; dbi-users@perl.org
    Subject: Re: SQL Query Question


    You need to add "GROUP BY Company" to the end of your SQL statement.
    I find that easy to forget, but the now-familiar error messages help!
    ----- Original Message -----
    From: "Isaac" <isaac@netos.com>
    To: <dbi-users@perl.org>
    Sent: Thursday, August 23, 2001 12:52 PM
    Subject: SQL Query Question

    Hello,

    I am not a db programmer so please excuse this probably simple question but
    I can't seem to figure it out.

    There is an Access 2000 database and inside this database there is a table
    called Customers.

    Customers has various columns and three of those are called Company,
    ThisMonth and Carrier

    The ThisMonth column contains how much bandwidth a particular company pushed
    and Carrier specifies
    which Telco they connect to.

    I am trying to write a SQL Query that gives me the customer who pushed the
    most bandwidth for a particular
    Carrier and also how much bandwidth that was.

    I have this so far:

    $total_all = $dbh->prepare (qq {SELECT MAX(ThisMonth) FROM Customers Where
    Carrier = 'Qwest'});

    That works but it just gives me the amount of bandwidth pushed and not the
    customer who pushed it.

    I have tried all manner of other queries to get BOTH the customer in
    particular and the MAX(ThisMonth) but they don't work.
    I get syntax errors being reported back thru the DBI.

    For instance I tried things like:

    $total_all = $dbh->prepare (qq {SELECT Company, MAX(ThisMonth) FROM
    Customers Where Carrier = 'Qwest'});

    and got this back:

    ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] You tried
    to execute a query that does not include the
    specified expression 'Company' as part of an aggregate function.
    (SQL-37000)(DBD: st_execute/SQLExecute err=-1) at D:\BotArchive
    \Bandwidth\Summary\test.pl line 117, <STDIN> line 1.
    Any ideas?

    Thanks,

    John




Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedAug 23, '01 at 5:52p
activeAug 24, '01 at 1:38p
posts4
users4
websitedbi.perl.org

4 users in discussion

Mark Sheinbaum: 1 post Wsheldah: 1 post Jeff Eckermann: 1 post Isaac: 1 post

Content

People

Support

Translate

site design / logo © 2022 Grokbase