470,648 Members | 1,608 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,648 developers. It's quick & easy.

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 17 '05 #1
4 1513
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 17 '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 17 '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 17 '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 17 '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
1 post views Thread by Korara | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.