Ad
How To Resolve Nested Aggregate Function Error?
I used case function to bucket aggregate counts of an item and want to divide the sum total of all counts into the value for each bucket (looking to have each bucket shown as a % of the total). However, I am getting an error that I cannot nest aggregate functions, which I understand, but need some help finding an alternate solution to achieve my goal.
Error:
Aggregate functions cannot be nested: [COUNT("values".CASE_AGE_CATEGORY)] nested in [SUM(COUNT("values".CASE_AGE_CATEGORY))]
Code:
SELECT Case_Age_Category, COUNT(Case_Age_Category)/sum(count(Case_Age_Category)) as Volume
FROM
(
SELECT DISTINCT(c.CASE_ID),c.CLOSED_AT,
CASE
WHEN TIMEDIFF('HOUR', ASSET_CHECKED_IN_AT, STATUS_CHANGED_TO_COMPLETE_HERE_AT) >0
AND TIMEDIFF('HOUR', ASSET_CHECKED_IN_AT, STATUS_CHANGED_TO_COMPLETE_HERE_AT) <24 then '0-24 HOURS'
WHEN TIMEDIFF('HOUR', ASSET_CHECKED_IN_AT, STATUS_CHANGED_TO_COMPLETE_HERE_AT) >24
AND TIMEDIFF('HOUR', ASSET_CHECKED_IN_AT, STATUS_CHANGED_TO_COMPLETE_HERE_AT) <48 then '24-48 HOURS'
WHEN TIMEDIFF('HOUR', ASSET_CHECKED_IN_AT, STATUS_CHANGED_TO_COMPLETE_HERE_AT) >48
AND TIMEDIFF('HOUR', ASSET_CHECKED_IN_AT, STATUS_CHANGED_TO_COMPLETE_HERE_AT) <72 then '48-72 HOURS'
WHEN TIMEDIFF('HOUR', ASSET_CHECKED_IN_AT, STATUS_CHANGED_TO_COMPLETE_HERE_AT) >72
AND TIMEDIFF('HOUR', ASSET_CHECKED_IN_AT, STATUS_CHANGED_TO_COMPLETE_HERE_AT) <96 then '72-96 HOURS'
WHEN TIMEDIFF('HOUR', ASSET_CHECKED_IN_AT, STATUS_CHANGED_TO_COMPLETE_HERE_AT) >96
AND TIMEDIFF('HOUR', ASSET_CHECKED_IN_AT, STATUS_CHANGED_TO_COMPLETE_HERE_AT) <120 then '96-120 HOURS'
else '>5 DAYS'
End as Case_Age_Category
FROM TEST_DB.STAGING.DW_DECISIV_CASES c inner join DB.Seed.DEALER_MAPPING d on c.DEALER_ID = d.DECISIVDEALERID
WHERE d.DIVISION = 'K'
and RO_NUMBER is not NULL
and (d.DEALERCATEGORY ILIKE 'DEALER' OR d.DEALERCATEGORY ILIKE 'RTC')
and d.DEALERUSAGE ILIKE 'PRODUCTION'
and d.OWNERGROUPCODE !='S040'
)
WHERE CLOSED_AT >= '2021-01-01 00:00:00.000'
GROUP BY Case_Age_Category
ORDER BY Case_Age_Category ASC
Screenshot of current output:
Ad
Answer
looking to have each bucket shown as a % of the total
Combining COUNT(...) with windowed SUM() OVER() to get the total across all groups:
SELECT Case_Age_Category,
DIV0(COUNT(Case_Age_Category), SUM(COUNT(Case_Age_Category)) OVER()) as Volume
FROM
(
-- ...
) sub
WHERE CLOSED_AT >= '2021-01-01 00:00:00.000'
GROUP BY Case_Age_Category
ORDER BY Case_Age_Category ASC
Ad
source: stackoverflow.com
Related Questions
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key
Ad