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.

Mureinik · Accepted Answer · 2015-12-14 23:08:16Z

3

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;

edited Dec 14, 2015 at 23:08

answered Dec 14, 2015 at 23:04

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL: multiple nested aggregate functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related