# Thread: How can I solve a MAX(COUNT())

1. Registered User
Join Date
Nov 2008
Posts
4

## Unanswered: How can I solve a MAX(COUNT())

Hello everybody.
I have these 2 tables

P (CodP, NameP, DoB, Nation)
Q (CodQ, Title, CodP)

The first one contains info about the painters: their code, their name, their date of birth, their nation.

The second one contains info about the paintings: their code, their title and their author's code.

I have to find out, for each nations, the painter who has produced the highest number of paintings, and how many paintings did he produce.

The solution I thought of, seems to be not available in SQL. Here it is:

SELECT P.CodP, COUNT(Q.CodQ) AS NumQ, Nation
FROM P, Q
WHERE P.CodP=Q.CodQ
GROUP BY CodP, Nation
HAVING MAX(NumQ)

I receive an error saying that NumQ is an unknown field.
If I don't declare COUNT(Q.CodQ) AS NumQ and use MAX(COUNT(Q.CodQ)) instead, the error says I cannot nest MAX and COUNT.
What is the solution to this problem?

Thank you

2. Registered User
Join Date
Nov 2008
Posts
4
it's hard to believe that no one knows how to solve an exercise of an introductory course on SQL.
How am I supposed to solve it, if I didn't even finished the course yet??

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
start by writing a query that returns the number of paintings for each painter

once you've done that, i'll show you the next step

4. Registered User
Join Date
Nov 2008
Posts
4
This is definitely easy. Just remove the last line from the solution I proposed.

SELECT P.CodP, COUNT(Q.CodQ) AS NumQ, Nation
FROM P, Q
WHERE P.CodP=Q.CodQ
GROUP BY CodP, Nation

And you'll get the number of paintings per each author!!

Umberto

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
remove the last line? like this?

SELECT P.CodP, COUNT(Q.CodQ) AS NumQ, Nation
FROM P, Q
WHERE P.CodP=Q.CodQ

here's a tip: set up a test database, populate some rows, and actually run your queries

this one's wrong in several different ways

6. Registered User
Join Date
Nov 2008
Posts
4
No... In my last post I had ALREADY removed the last line.

The query you asked for it's:

SELECT P.CodP, COUNT(Q.CodQ) AS NumQ, Nation
FROM P, Q
WHERE P.CodP=Q.CodQ
GROUP BY CodP, Nation

I tested it on my personal sql space and it works perfectly. It gives me the number of paintings per each author. But I have to move on and find, for each nation, the author who made the most paintings :-)

Umberto

7. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
actually, that query returns more than what i asked for

here's the query to return the the number of paintings per author --
Code:
```SELECT CodP
, COUNT(*) AS paintings
FROM Q
GROUP
BY CodP```
now i know this next point is a minor point, but it's important, and if you're interested in learning (rather than "moving on" to finish an assignment on time), you'll try to figure out why

after you have the counts per painter, then you join to the painter table to find his country --
Code:
```SELECT P.CodP
, P.NameP
, P.DoB
, P.Nation
, COALESCE(C.paintings,0) AS paintings
FROM P
LEFT OUTER
JOIN ( SELECT CodP
, COUNT(*) AS paintings
FROM Q
GROUP
BY CodP ) AS C
ON C.CodP = P.CodP```
does your assignment specifically stipulate MySQL or is it non-denominational?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•