Can’t figure out Query and Sub-Queries

Question

I’m having trouble figuring this problem out. I’m doing some revision exercises for university and would like to understand this BEFORE my exam in 2 days.

I’ve attempted some things (which I’ll post at the end). Please be kind, this is my first Database subject so my attempts may seem very stupid to you.

The question is as follows: Which artist/s has/have the largest number of shows on at the moment? Show the First & Last Name of the artist/s and their Address. ORDER BY clause cannot be used. Write a single SQL Statement. Use Sub-Queries.

Relevant tables in the database:

Shows (ShowName, ArtistId, ShowStartDate, ShowEndDate)
Artists (ArtistId, FirstName, FamilyName, Address, PhoneNum)

We assume ArtistId, ShowStartDate, FirstName, FamilyName and Address cannot be null.

Now, I think that I have to count the number of shows each artist has on at the moment. Then, get the ArtistId for the artist/s that has/have the most. Use the ArtistId to retrieve the artist details (names and address).

I got as far as this (which is very wrong):

SELECT FirstName, FamilyName, Address
FROM Artists
WHERE ArtistId = (SELECT ArtistId
                  FROM Shows
                  WHERE ArtistId = (SELECT MAX(Counted) 
                                    FROM (SELECT ArtistId, COUNT(ArtistId) AS Counted
                                    FROM Shows
                                    WHERE ShowEndDate IS null
                                    GROUP BY ArtistId)
                  GROUP BY ArtistId));

Well, I know

SELECT ArtistId, COUNT(ArtistId)
FROM Shows
WHERE ShowEndDate IS null
GROUP BY ArtistId

gives me a table with the count of how many times each ArtistId is listed. Which is good. But from this results table, I need to get the ArtistId/’s of the ones that have the highest count.

And this is where I’m lost.

Anyone can shed some light?

(As for which DBMS I am using: We have to use one created and supplied by the university. It’s very basic SQL. Simpler than Access 2010).

Thank you

(If you provide an answer [thank you thank you] could you also briefly explain the reasoning behind it?)

Answer 1

You need to find maximum of the count of shows by artist, then find out which artists have that count by re-running the count query but applying a having clause matching the maximum just found.

select FirstName, FamilyName, Address
from Artists
where ArtistId in -- use an in() to select the artists
  (select ArtistId from -- just select the artist id from the results
    (select ArtistId, count(*) c -- re-run the count query, but see having clause
     from Shows
     where current_date between ShowStartDate and ShowEndDate
     group by ArtistId
     having count(*) = -- use a having clause to only select those with the max count
      (select max(c) from -- this is simply the maximum count
        (select ArtistId, count(*) c -- find all counts by artist
         from Shows
         where current_date between ShowStartDate and ShowEndDate
         group by ArtistId
        ) counts
      )
    )
  )

Some syntax notes:

• count(*) c means the column (with value count(*)) is given the alias c, so it can be referred to by an outer query. You can't refer to it as count(*), because that would be interpreted as an attempt at aggregation.
• max(c) gets the maximum of the column named (or aliased) c (AFAIK you can't code max(count(*)) - maybe you could try it - I just typed this in without a console to test it)
• counts is a table alias, which is a syntactic requirement when selecting from a result set

You haven't specified which database you're using, so you may have to replace current_date with your database's equivalent.

Some dbs allow you to reuse a query in a query (using a with clause), which would avoid rerunning the count subquery.

This query uses only subselects, but you can do it with a join too.

Answer 2

Try this:

SELECT FirstName, FamilyName, Address
FROM Artists
WHERE ArtistId IN (
    SELECT ArtistId
    FROM (
        SELECT ArtistId, COUNT(ArtistId) AS Counted
        FROM Shows
        WHERE ShowEndDate IS null
        GROUP BY ArtistId) S1
    WHERE Counted = (
        SELECT MAX(Counted) 
        FROM (
            SELECT ArtistId, COUNT(ArtistId) AS Counted
            FROM Shows
            WHERE ShowEndDate IS null
            GROUP BY ArtistId) S2
        GROUP BY ArtistId)
    );

It is simple and should work in your case.