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 <[EMAIL PROTECTED]> on 08/23/2001
02:34:06 PM
To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
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 [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 23, 2001 11:28 AM
To: Isaac; [EMAIL PROTECTED]
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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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
>
>
>
>
>
>
