Diversity index MS SQL

ShannonIndex.sql

WITH SpeciesCounts AS (
    SELECT
        SpecieType,
        COUNT(*) AS CountPerSpecies
    FROM
        Table
    GROUP BY
        SpecieType
),
TotalCount AS (
    SELECT
		SUM(CountPerSpecies) as TotalRowCount,
        COUNT(CountPerSpecies) AS TotalSpeciesCount
    FROM
        SpeciesCounts
)
SELECT
    (SELECT SUM(CountPerSpecies) FROM SpeciesCounts) AS TotalRowCount,
    (SELECT COUNT(DISTINCT SpecieType) FROM SpeciesCounts) AS DifferentSpeciesCount,
    (SELECT MAX(CountPerSpecies) FROM SpeciesCounts) AS MaxCountPerSpecies,
    -SUM((CountPerSpecies * 1.0 / TotalSpeciesCount) * LOG(CountPerSpecies * 1.0 / TotalSpeciesCount)) AS ShannonIndex
FROM
    SpeciesCounts
CROSS JOIN
    TotalCount;