By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,271 Members | 1,294 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,271 IT Pros & Developers. It's quick & easy.

help with complex SQL query

P: n/a
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
Share this Question
Share on Google+
4 Replies

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.