Instar percentages per year per region (summary data)

Custom SQL query returning 603 rows (hide)

SELECT Country, Tree, OriginalRegion AS Location, CAST(printf('%.6f', Longitude) AS FLOAT) AS Longitude, CAST(printf('%.6f', Latitude) AS FLOAT) AS Latitude, CAST(Elevation AS FLOAT) AS Elevation, strftime('%Y', Date) AS Year, SUBSTR( 'JanFebMarAprMayJunJulAugSepOctNovDec', 1 + 3 * STRFTIME('%m', date(Date)), -3 ) AS Month, CAST(STRFTIME('%j', Date) AS INTEGER) AS Julian, Instar, CAST( CASE WHEN Instar = 'Empty' THEN 6 ELSE SUBSTR(Instar, 2, 1) END AS INTEGER ) AS Stage, CAST( ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ( PARTITION BY OriginalRegion, strftime('%Y', Date) ), 1) AS FLOAT ) AS 'Year %', CAST( ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ( PARTITION BY OriginalRegion ), 1) AS FLOAT ) AS 'Overall %', CAST(COUNT(*) AS INTEGER) AS 'n', CAST(SUM(COUNT(*)) OVER (PARTITION BY OriginalRegion) AS INTEGER) AS 'Total n in location' FROM pclim GROUP BY Location, Year, Month, Instar ORDER BY Year DESC, Country, Location, Month DESC, Stage LIMIT 10000;

Edit SQL