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

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??

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

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!!


Thanks in advance for your time!

Umberto

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

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

actually, that query returns more than what i asked for

here's the query to return the the number of paintings per author --
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 --
does your assignment specifically stipulate MySQL or is it non-denominational?