help with complex SQL query

[NotGiven]

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
....

[Bill Karwin]

Re: help with complex SQL query

NotGiven wrote:[color=blue]
> 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
> ...
>
>[/color]

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 ProjCountByClie ntAndStatus
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.

[Michael Austin]

Re: help with complex SQL query

Bill Karwin wrote:
[color=blue]
> NotGiven wrote:
>[color=green]
>> 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
>> ...
>>
>>[/color]
>
> 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 ProjCountByClie ntAndStatus
> 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.[/color]

or you can use a left outer join as well...

similar to:
select a.b as name, count(*) as jan,
count(*) as feb,
count(*) as mar
from testb a
left outer join testa b on a.a = b.a
and extract(year from b.dt) = '2004' and extract(MONTH from b.dt) = '1'
left outer join testa c on a.a = c.a
and extract(year from c.dt) = '2004' and extract(MONTH from c.dt) = '2'
left outer join testa d on a.a = d.a
and extract(year from d.dt) = '2004' and extract(MONTH from d.dt) = '3'
group by a.b, b.c, c.c, d.c;

NAME JAN FEB MAR
BMW 1 1 1
FORD 1 1 1
GMC 1 1 1

the actual column names will be left as an excercise for the OP.

--
Michael Austin.
Consultant - NOT Available.
Donations STILL welcomed. Http://www.firstdbasource.com/donations.html
:)

[NotGiven]

Re: help with complex SQL query

Would this work with MySQL 3.34?


"Michael Austin" <maustin@firstd basource.com> wrote in message
news:1V6Xc.6147 $Di7.1141@newss vr24.news.prodi gy.com...[color=blue]
> Bill Karwin wrote:
>[color=green]
> > NotGiven wrote:
> >[color=darkred]
> >> I have two tables, Client and Project, related by clientID.
> >>
> >> I want to display a table with total CURRENT projects and total[/color][/color][/color]
COMPLETED[color=blue][color=green][color=darkred]
> >> 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
> >> ...
> >>
> >>[/color]
> >
> > 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 ProjCountByClie ntAndStatus
> > 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.[/color]
>
> or you can use a left outer join as well...
>
> similar to:
> select a.b as name, count(*) as jan,
> count(*) as feb,
> count(*) as mar
> from testb a
> left outer join testa b on a.a = b.a
> and extract(year from b.dt) = '2004' and extract(MONTH from b.dt)[/color]
= '1'[color=blue]
> left outer join testa c on a.a = c.a
> and extract(year from c.dt) = '2004' and extract(MONTH from c.dt)[/color]
= '2'[color=blue]
> left outer join testa d on a.a = d.a
> and extract(year from d.dt) = '2004' and extract(MONTH from d.dt)[/color]
= '3'[color=blue]
> group by a.b, b.c, c.c, d.c;
>
> NAME JAN FEB MAR
> BMW 1 1 1
> FORD 1 1 1
> GMC 1 1 1
>
> the actual column names will be left as an excercise for the OP.
>
> --
> Michael Austin.
> Consultant - NOT Available.
> Donations STILL welcomed. Http://www.firstdbasource.com/donations.html
> :)[/color]

[NotGiven]

Re: help with complex SQL query

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.co m> wrote in message
news:cgis4u0274 4@enews3.newsgu y.com...[color=blue]
> NotGiven wrote:[color=green]
> > I have two tables, Client and Project, related by clientID.
> >
> > I want to display a table with total CURRENT projects and total[/color][/color]
COMPLETED[color=blue][color=green]
> > 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
> > ...
> >
> >[/color]
>
> 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 ProjCountByClie ntAndStatus
> 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.[/color]