Re: help with complex SQL query

From: NotGiven (noname_at_nonegiven.net)
Date: 08/26/04 

Date: Wed, 25 Aug 2004 19:00:24 -0400

thanks - great suggestions and I'll try it.

I am using a hosting company and I've found most all hosting companies use
MySQL 3.34 - something to do with the licensing agreement changing after
that verison.

"Bill Karwin" <bill@karwin.com> wrote in message
news:cgis4u02744@enews3.newsguy.com...
> NotGiven wrote:
> > I have two tables, Client and Project, related by clientID.
> >
> > I want to display a table with total CURRENT projects and total
COMPLETED
> > projects per client.
> >
> > I'd like for it to show like this:
> >
> > Client Name Current Proj Completed Proj
> > Stan's Dormers 4 12
> > Larry Lincoln Dealership 2 1
> > ...
> >
> >
>
> As you've no doubt discovered, this is fairly easy if you only want one
> or the other status (current or completed). But if you want both,
> you're in effect trying to get totals grouped by status while outputting
> grouped by clientId. This is a logical impossibility.
>
> The only solution I can think of offhand is to use correlated subqueries
> in the select-list. You'd need to be running MySQL 4.1 for this query
> to work.
>
> SELECT C.ClientName,
> (SELECT COUNT(*) FROM Project P
> WHERE P.clientID = C.clientID AND P.Status = 'CURRENT')
> AS CurrentProj,
> (SELECT COUNT(*) FROM Project P
> WHERE P.clientID = C.clientID AND P.Status = 'COMPLETED')
> AS CompletedProj,
> FROM Client C;
>
> Another option, which should work in earlier versions of MySQL, is to
> prepare the right output in _almost_ the right format, and then reformat
> the results in your application code.
>
> SELECT C.ClientName, P.Status, COUNT(*) AS ProjCountByClientAndStatus
> FROM Project P INNER JOIN Client C ON P.clientID = C.clientID
> GROUP BY C.ClientName, P.Status;
>
> SQL was never meant to be a complete programming language. It assumes
> that you will do some manipulation of the results in an application.
>
> Regards,
> Bill K.

Relevant Pages

  • Re: Some free utilities for Java, with Hebrew support.
    ... mostly separate from the one for replacing the client library. ... Then you're a MySQL reseller, by the sounds of it, rather than a MySQL ... I.e. they might use MySQL server and client, ... competing libraries, so I'd expect to see it happen sooner or later. ...
    (comp.lang.java.programmer)
  • Re: Confusion about database updates
    ... all connecting to the same database server. ... MySQL can easily handle many simultaneous clients. ... AlphaCluster all open multiple connections to a MySQL server running on ... Let the MySQL server do that when your client ...
    (comp.lang.java.databases)
  • Re: commands out of sync problem
    ... server and client application like this: ... Add a SQLconnection for the MySQL database ... and...tada 'commands out of sync: you can't run this command now'. ...
    (alt.comp.lang.borland-delphi)
  • Re: MySQL in installed but mysqld nowhere to be found!!!!
    ... If you choose to install MySQL ... > there is a good possibility that you will run the server. ... think that on any system you'd more likely need client stuff. ... As for "customer relationships" well...you didn't get there to find out ...
    (RedHat)
  • Re: PHP front to mySQL database
    ... You should be able to set up MySQL to respond via ODBC to calls from VB. ... Set up the database file table structure in Access and call it from VB. ... I also prefer to use the web browser as the client interface instead of VB. ... an installation pain. ...
    (alt.php)