asked on
ASKER
ASKER
ASKER
Then does the count I suggested work?throws a "not a group by expression" error with the new count statement
ASKER
select USERS.BUILDINGID,
USERS.GRADE_LEVEL,
count(USERS.ETHNICITY) as Total_USERS_Per_Grade,
count(case when USERS.ETHNICITY != '04' then 1 end) as Ethnic_User_Count,
count(case when S_NY_STU_CODE_X.CODEEDUCATION = 'Y' then 1 end) as CODE_Ed_User_Count,
round(count(case when USERS.ETHNICITY != '04' then 1 end)/count(USERS.ETHNICITY)*100,2)||'%' as Ethnic_User_Count,
sum(count(case when USERS.ETHNICITY != '04' then 1 end)) over(partition by BUILDINGID) as building_User_Count
FROM
PS.USERS USERS
LEFT JOIN PS.S_NY_STU_CODE_X S_NY_STU_CODE_X
ON PS.USERS.DCID = PS.S_NY_STU_CODE_X.USERSDCID
group by BUILDINGID, GRADE_LEVEL
)
ASKER
ASKER
ASKER
ASKER
ASKER
ASKER
select BUILDINGID,
GRADE_LEVEL,
Total_USERS_Per_Grade,
Ethnic_User_Count,
CODE_Ed_User_Count,
Ethnic_User_Count,
case when rn=1 then building_User_Count end building_user_count,
case when rn=1 then GRADE_User_Count end GRADE_user_count
from (
select USERS.BUILDINGID,
USERS.GRADE_LEVEL,
count(USERS.ETHNICITY) as Total_USERS_Per_Grade,
count(case when USERS.ETHNICITY != '04' then 1 end) as Ethnic_User_Count,
count(case when S_NY_STU_CODE_X.CODEEDUCATION = 'Y' then 1 end) as CODE_Ed_User_Count,
round(count(case when USERS.ETHNICITY != '04' then 1 end)/count(USERS.ETHNICITY)*100,2)||'%' as Ethnic_User_Count,
sum(count(case when USERS.ETHNICITY != '04' then 1 end)) over(partition by BUILDINGID) as building_User_Count,
sum(count(case when USERS.ETHNICITY != '04' then 1 end)) over(partition by GRADE_LEVEL) as GRADE_User_Count,
row_number() over(partition by buildingid order by BUILDINGID, GRADE_LEVEL) rn
FROM
PS.USERS USERS
LEFT JOIN PS.S_NY_STU_CODE_X S_NY_STU_CODE_X
ON PS.USERS.DCID = PS.S_NY_STU_CODE_X.USERSDCID
group by BUILDINGID, GRADE_LEVEL
)
ASKER
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
TRUSTED BY