Basssque
asked on
Adding a total based on the query results
Is it possible to add two columns to the Oracle query at the bottom based on my criteria below?
I need the sum of the total instances where 'Y' exists in S_CT_STU_SPED_X.SPECIALEDU
This is contradictory to the current set of results that I'm calling because I need to group the existing query statements by buildingid AND grade_level
Lets say
buildingid 1 and 2 have grade_levels 6 and 7
buildingid 3 has grade levels of 6, 7, and 8
buildingid 4 has grade levels of 8, 9, 10, 11, 12
and building 5 has grade levels of 1 through 12
I'd need to total all instances of 'Y' in records that exist in S_CT_STU_SPED_X.SPECIALEDU
The same for grade_level 8 in building 3, 4 and 5
etc.
I was thinking something like a partition by statement would work but can't quite figure it out.
The second new column would total the number of records in STUDENTS.ETHNICITY not equal to '04' by buildingid only, NOT grade level
It seems that the group by statement that applies to the entire query is whats tripping me up the most here.
I'd also like to avoid having duplicate rows containing the same results in these 2 new columns if possible.
Thanks!!!
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.CODEEDUCAT
round(count(case when USERS.ETHNICITY != '04' then 1 end)/count(USERS.ETHNICITY
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.USERSDC
group by BUILDINGID, GRADE_LEVEL
order by BUILDINGID
I need the sum of the total instances where 'Y' exists in S_CT_STU_SPED_X.SPECIALEDU
This is contradictory to the current set of results that I'm calling because I need to group the existing query statements by buildingid AND grade_level
Lets say
buildingid 1 and 2 have grade_levels 6 and 7
buildingid 3 has grade levels of 6, 7, and 8
buildingid 4 has grade levels of 8, 9, 10, 11, 12
and building 5 has grade levels of 1 through 12
I'd need to total all instances of 'Y' in records that exist in S_CT_STU_SPED_X.SPECIALEDU
The same for grade_level 8 in building 3, 4 and 5
etc.
I was thinking something like a partition by statement would work but can't quite figure it out.
The second new column would total the number of records in STUDENTS.ETHNICITY not equal to '04' by buildingid only, NOT grade level
It seems that the group by statement that applies to the entire query is whats tripping me up the most here.
I'd also like to avoid having duplicate rows containing the same results in these 2 new columns if possible.
Thanks!!!
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.CODEEDUCAT
round(count(case when USERS.ETHNICITY != '04' then 1 end)/count(USERS.ETHNICITY
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.USERSDC
group by BUILDINGID, GRADE_LEVEL
order by BUILDINGID
Last Comment
can you post sample data and expected results?
While we wait for the data and expected results, I'll guess:
something like this?
count(case when USERS.ETHNICITY != '04' then 1 end) over(partition by USERS.BUILDINGID) as building_User_Count
The issue there is the building count will appear on every grade level row.
something like this?
count(case when USERS.ETHNICITY != '04' then 1 end) over(partition by USERS.BUILDINGID) as building_User_Count
The issue there is the building count will appear on every grade level row.
ASKER
Current Results Example
BUILDINGID,GRADE_LEVEL,Tot
1,6,1000,222,12,22.2%
1,7,1000,223,13,22.3%
2,6,1000,225,14,22.5%
2,7,1000,226,15,22.6%
3,6,1000,227,16,22.7%
3,7,1000,228,17,22.8%
3,8,1000,229,18,22.9%
Desired Results Example
BUILDINGID,GRADE_LEVEL,Tot
1,6,1000,222,12,22.2%
1,7,1000,223,13,22.3%
2,6,1000,225,14,22.5%
2,7,1000,226,15,22.6%
3,6,1000,227,16,22.7%
3,7,1000,228,17,22.8%
3,8,1000,229,18,22.9%
,6,3000,674,,
,7,3000,667,,
,8,2000,229,,
1,,,15,
2,,,29,
3,,,35,
OR
BUILDINGID,GRADE_LEVEL,Tot
1,6,1000,222,12,22.2%,,
1,7,1000,223,13,22.3%,,
2,6,1000,225,14,22.5%,,
2,7,1000,226,15,22.6%,,
3,6,1000,227,16,22.7%,,
3,7,1000,228,17,22.8%,,
3,8,1000,229,18,22.9%,,
,6,3000,,,,674,
,6,3000,,,,667,
,6,2000,,,,229,
1,,,,,,,15
1,,,,,,,29
1,,,,,,,35
Hope that gives a better idea.
BUILDINGID,GRADE_LEVEL,Tot
1,6,1000,222,12,22.2%
1,7,1000,223,13,22.3%
2,6,1000,225,14,22.5%
2,7,1000,226,15,22.6%
3,6,1000,227,16,22.7%
3,7,1000,228,17,22.8%
3,8,1000,229,18,22.9%
Desired Results Example
BUILDINGID,GRADE_LEVEL,Tot
1,6,1000,222,12,22.2%
1,7,1000,223,13,22.3%
2,6,1000,225,14,22.5%
2,7,1000,226,15,22.6%
3,6,1000,227,16,22.7%
3,7,1000,228,17,22.8%
3,8,1000,229,18,22.9%
,6,3000,674,,
,7,3000,667,,
,8,2000,229,,
1,,,15,
2,,,29,
3,,,35,
OR
BUILDINGID,GRADE_LEVEL,Tot
1,6,1000,222,12,22.2%,,
1,7,1000,223,13,22.3%,,
2,6,1000,225,14,22.5%,,
2,7,1000,226,15,22.6%,,
3,6,1000,227,16,22.7%,,
3,7,1000,228,17,22.8%,,
3,8,1000,229,18,22.9%,,
,6,3000,,,,674,
,6,3000,,,,667,
,6,2000,,,,229,
1,,,,,,,15
1,,,,,,,29
1,,,,,,,35
Hope that gives a better idea.
So you want two different result sets not a new column?
Two queries and a UNION ALL between them.
Two queries and a UNION ALL between them.
ASKER
New columns would be preferred...
I guess I should stick to the topic, the different results were just a thought
I guess I should stick to the topic, the different results were just a thought
>>New columns would be preferred...
Then does the count I suggested work?
When posting the requested data, we really need raw data not existing results vs new results. We use the raw data to set up test cases so we can post working and tested code.
Then does the count I suggested work?
When posting the requested data, we really need raw data not existing results vs new results. We use the raw data to set up test cases so we can post working and tested code.
ASKER
Then does the count I suggested work?throws a "not a group by expression" error with the new count statement
Guess #2: Use an inline view and move the count outside of the main query.
select BUILDINGID, GRADE_LEVEL, Total_USERS_Per_Grade, Ethnic_User_Count, CODE_Ed_User_Count, Ethnic_User_Count,
count(case when ETHNICITY != '04' then 1 end) over(partition by BUILDINGID) as building_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.CODEEDUCAT
round(count(case when USERS.ETHNICITY != '04' then 1 end)/count(USERS.ETHNICITY
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.USERSDC
group by BUILDINGID, GRADE_LEVEL
)
order by BUILDINGID
select BUILDINGID, GRADE_LEVEL, Total_USERS_Per_Grade, Ethnic_User_Count, CODE_Ed_User_Count, Ethnic_User_Count,
count(case when ETHNICITY != '04' then 1 end) over(partition by BUILDINGID) as building_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.CODEEDUCAT
round(count(case when USERS.ETHNICITY != '04' then 1 end)/count(USERS.ETHNICITY
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.USERSDC
group by BUILDINGID, GRADE_LEVEL
)
order by BUILDINGID
ASKER
slightvw
That is close.
The only issue there is the following statement throws an invalid identifier for ETHNICITY
count(case when ETHNICITY != '04' then 1 end) over(partition by BUILDINGID) as building_User_Count
I tried to change it to USERS.ETHNICITY but then I get invalid identifier for USERS.ETHNICITY
If I comment that statement out, it runs successfully but I don't get the building_user_count column output in that case.
That is close.
The only issue there is the following statement throws an invalid identifier for ETHNICITY
count(case when ETHNICITY != '04' then 1 end) over(partition by BUILDINGID) as building_User_Count
I tried to change it to USERS.ETHNICITY but then I get invalid identifier for USERS.ETHNICITY
If I comment that statement out, it runs successfully but I don't get the building_user_count column output in that case.
AH yes, ETHNICITY isn't a column in the inner query so it cannot be referenced.
Give me a minute to dig a little deeper.
Give me a minute to dig a little deeper.
Raw sample data and expected results would help a lot. Then I can actually test code before I post it.
Until then, they are guesses.
Try this:
Until then, they are guesses.
Try this:
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
What one works!
Is there any way to prevent the value from repeating on every row associated with the column specified in the partition by statement (BUILDINGID)?
Only need one instance of the building_user_count per unique value in the BUILDINGID Column.
Other than that this looks really good. Thanks!!!
Is there any way to prevent the value from repeating on every row associated with the column specified in the partition by statement (BUILDINGID)?
Only need one instance of the building_user_count per unique value in the BUILDINGID Column.
Other than that this looks really good. Thanks!!!
>>Only need one instance of the building_user_count per unique value in the BUILDINGID Column.
Which row in the output would you like it on?
Which row in the output would you like it on?
ASKER
I suppose the first row of each buildingid instance would be fine
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Great!
And last but not least, how can I add the second additional column ordered by grade_level?
And last but not least, how can I add the second additional column ordered by grade_level?
Sorry but I don't understand what this new column does?
ASKER
The function we just added specifies that the building_user_count outputs only a single value in the first row of each buildingid instance in the buildingid column (based on the rn= statement)
sum(count(case when USERS.ETHNICITY != '04' then 1 end)) over(partition by BUILDINGID) as building_User_Count,
The other new column that I'm referring to performs a similar function
It totals the sum of each grade_level and should only output a single value for the first instance of each grade_level in the grade_level column (need another rn= statement to
sum(count(case when USERS.ETHNICITY != '04' then 1 end)) over(partition by GRADE_LEVEL) as GRADE_User_Count,
I tried adding it and it throws column ambiguously defined.
sum(count(case when USERS.ETHNICITY != '04' then 1 end)) over(partition by BUILDINGID) as building_User_Count,
The other new column that I'm referring to performs a similar function
It totals the sum of each grade_level and should only output a single value for the first instance of each grade_level in the grade_level column (need another rn= statement to
sum(count(case when USERS.ETHNICITY != '04' then 1 end)) over(partition by GRADE_LEVEL) as GRADE_User_Count,
I tried adding it and it throws column ambiguously defined.
>>adding it and it throws column ambiguously defined.
More than one of your tables has a GRADE_LEVEL column. Fully qualify it with the table name you want counted.
Given the rest of your query:
sum(count(case when USERS.ETHNICITY != '04' then 1 end)) over(partition by USERS.GRADE_LEVEL) as GRADE_User_Count,
More than one of your tables has a GRADE_LEVEL column. Fully qualify it with the table name you want counted.
Given the rest of your query:
sum(count(case when USERS.ETHNICITY != '04' then 1 end)) over(partition by USERS.GRADE_LEVEL) as GRADE_User_Count,
ASKER
Same result after adding the table as suggested
Post the query causing the error
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
)
You have two results aliased as Ethnic_User_Count.
Change one of the aliases:
count(case when USERS.ETHNICITY != '04' then 1 end) as Ethnic_User_Count,
round(count(case when USERS.ETHNICITY != '04' then 1 end)/count(USERS.ETHNICITY
Then change the selected column in the outer query to reflect the new name.
Change one of the aliases:
count(case when USERS.ETHNICITY != '04' then 1 end) as Ethnic_User_Count,
round(count(case when USERS.ETHNICITY != '04' then 1 end)/count(USERS.ETHNICITY
Then change the selected column in the outer query to reflect the new name.
ASKER
slightly embarrassing, thanks :-)
Oracle Database
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.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
