Here's a neat little script to get row stats for UPI/NUPI/USI/NUSI selection on the_table.the_row

Database
Enthusiast

Here's a neat little script to get row stats for UPI/NUPI/USI/NUSI selection on the_table.the_row

One day I'll add the access frequency data... unless somebody else wants to beat me :)

WITH the_table_summary(the_row, _count, _rank)
AS
(
SEL the_row, COUNT(the_row), RANK() OVER (ORDER BY COUNT(the_row) DESC)
FROM the_table
GROUP BY the_row
)
SEL CAST('total records' AS VARCHAR(255)), COUNT(the_row) FROM the_table
UNION ALL
SEL 'max rows/val', MAX(_count) FROM the_table_summary
UNION ALL
SEL 'mode rows/val', _count FROM the_table_summary QUALIFY RANK() OVER (ORDER BY COUNT(_count) DESC) =1 GROUP BY _count
UNION ALL
SEL 'total NULL', COUNT(CASE WHEN the_row IS NULL THEN 1 ELSE 0 END) FROM the_table WHERE the_row IS NULL GROUP BY 1