Results 1 to 7 of 7
  1. #1
    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. #2
    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. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    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!!


    Thanks in advance for your time!

    Umberto

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    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. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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