471,092 Members | 1,504 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

help with complex SQL query

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
....
Jul 20 '05 #1
4 1505
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.
Jul 20 '05 #2
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
:)
Jul 20 '05 #3
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
:)

Jul 20 '05 #4
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.

Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Starbuck | last post: by
1 post views Thread by Eva | last post: by
14 posts views Thread by  | last post: by
5 posts views Thread by Justin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.