I'd like some help to write sql code to return a list of customer data items ranked by frequency (high to low)

Question

The table I am querying has several thousand rows and numerous fields - I'd like the code to return the top 10 values for a handful of the fields, namely: Forename, Surname and City - I'd also like to see a count of the values returned.

For example

Ranking	Forename	FName Frequency	Surname	SName Frequency	City	City Frequency
1	Liam	830,091	Smith	2,353,709	New York	2,679,785
2	Mary	708,390	Johnson	1,562,990	Los Angeles	413,359
3	Noah	639,592	Williams	792,306	Chicago	393,511
4	Patricia	568,410	Brown	743,346	Houston	367,496
5	William	557,049	Jones	633,933	Phoenix	336,929
6	Linda	497,138	Miller	503,523	Philadelphia	304,638
7	James	490,665	Davis	503,115	San Antonio	255,142
8	Barbara	418,312	Garcia	468,683	San Diego	238,521
9	Logan	399,947	Rodriguez	461,816	Dallas	232,718
10	Elizabeth	399,737	Wilson	436,843	San Jose	213,483

The returned list should be interpreted thus: The most frequently occurring forename in the table is Liam - with 830,091 instances, The 5th most frequently occurring forename is William - with 557,049 instances, The 8th most frequently occurring city is San Diego - with 238,521 instances ...and so on

(N.b. the table does not show there are 2.7m Liams in New York - just that there are 830,091 Liams in the entire table - and that there are 2,679,785 New York addresses in the entire table)

The following produces what I need - but just for the first field (Forename) - I'd like to be able to do the same for three fields

SELECT Forename, COUNT(Forename) AS FName_Frequency
FROM Customer_Table
GROUP BY Forename
ORDER BY FName_Frequency DESC
limit 10

Thanks in anticipation

Answer 1

I would just put this in separate rows:

select 'forename', forename, count(*) as freq
from customer_table
group by forename
order by freq desc
fetch first 10 rows only
union all
select 'surname', surname, count(*) as freq
from customer_table
group by surname
order by freq desc
fetch first 10 rows only
union all
select 'city', city, count(*) as freq
from customer_table
group by city
order by freq desc
fetch first 10 rows only;

Note that this uses Standard SQL syntax, because you have not tagged with the question with the database you are using. You can also put this in separate columns, using:

select max(case when which = 'forename' then col end),
       max(case when which = 'forename' then freq end),
       max(case when which = 'surname' then col end),
       max(case when which = 'surname' then freq end),
       max(case when which = 'city' then col end),
       max(case when which = 'city' then freq end)
from ((select 'forename' as which, forename as col, count(*) as freq,
              row_number() over (order by count(*) desc) as seqnum
       from customer_table
       group by forename
      ) union all
      (select 'surname' as which, surname, count(*) as freq
              row_number() over (order by count(*) desc) as seqnum
       from customer_table
       group by surname
      ) union all
      (select 'city', city, count(*) as freq,
              row_number() over (order by count(*) desc) as seqnum
       from customer_table
       group by city
      )
     ) x
group by seqnum;