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]

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.

[Michael Austin]

Bill Karwin wrote:

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.

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]

Would this work with MySQL 3.34?
"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:1V*****************@newssvr24.news.prodigy.co m...

Bill Karwin wrote:

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.

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]

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" <bi**@karwin.com> wrote in message
news:cg*********@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.