SQL: multiple nested aggregate functions

Question

I have a PostgreSQL table that looks like:

artists | songs  
===================
artist1 | song a
artist1 | song b
artist2 | song c

and I want to make a select statement that gives me for every artist the number of tracks and the difference between the number of his tracks and the number of the artist with the most tracks

so in this case

artist  | number songs | difference
====================================
artist1 | 2            | 0
artist2 | 1            | 1

The problem I'm having is that I am using count(songs) for the number of songs and also max(count(songs)) (needed to calculate the difference) in the same result And using both gives me problems with nested aggregated functions.

Answer 1

You can use a combination of aggregate functions (to count the number of songs per artists) and window functions to produce this result:

SELECT   artist, 
         COUNT(*) AS num_songs, 
         MAX(COUNT(*)) OVER (ORDER BY COUNT(*) DESC) - COUNT(*) AS diff
FROM     artists                                                       
GROUP BY artist;

It's a tad clunky, but it doe the trick.

EDIT:
As commented by @a_horse_with_no_name, the order by clause in the over clause is redundant. Removing it definitely makes the code easier to read:

SELECT   artist, 
         COUNT(*) AS num_songs, 
         MAX(COUNT(*)) OVER () - COUNT(*) AS diff
FROM     artists                                                       
GROUP BY artist;