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.SPECIALEDUCATION for each USERS.GRADE_LEVEL record and output a total in a new column grouped by USERS.GRADE_LEVEL only
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.SPECIALEDUCATION for grade_level 7 across building 1, 2 and 5
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.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
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
order by BUILDINGID

Sean Stuber

can you post sample data and expected results?

slightwv (䄆 Netminder)

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.

Basssque

ASKER

Current Results Example
BUILDINGID,GRADE_LEVEL,Total_USERS_Per_Grade,Ethnic_User_Count,CODE_Ed_User_Count,Ethnic_User_Count
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,Total_USERS_Per_Grade,Ethnic_User_Count,CODE_Ed_User_Count,Ethnic_User_Count,CODE_Ed_User_Count_Per_Grade
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,Total_USERS_Per_Grade,Ethnic_User_Count,CODE_Ed_User_Count,Ethnic_User_Count,CODE_Ed_User_Count_Per_Grade,Ethnic_Users_Per_Building
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.

slightwv (䄆 Netminder)

So you want two different result sets not a new column?

Two queries and a UNION ALL between them.

Basssque

ASKER

New columns would be preferred...
I guess I should stick to the topic, the different results were just a thought

slightwv (䄆 Netminder)

>>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.

Basssque

ASKER

Then does the count I suggested work?

throws a "not a group by expression" error with the new count statement

slightwv (䄆 Netminder)

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.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
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
)
order by BUILDINGID

Basssque

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.

slightwv (䄆 Netminder)

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.

slightwv (䄆 Netminder)

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:

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
 )

Open in new window

Basssque

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!!!

slightwv (䄆 Netminder)

>>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?

Basssque

ASKER

I suppose the first row of each buildingid instance would be fine

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Basssque

ASKER

Great!
And last but not least, how can I add the second additional column ordered by grade_level?

slightwv (䄆 Netminder)

Sorry but I don't understand what this new column does?

Basssque

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.

slightwv (䄆 Netminder)

>>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,

Basssque

ASKER

Same result after adding the table as suggested

slightwv (䄆 Netminder)

Post the query causing the error

Basssque

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
 )

Open in new window

slightwv (䄆 Netminder)

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)*100,2)||'%' as Ethnic_User_Count,

Then change the selected column in the outer query to reflect the new name.

Basssque

ASKER

slightly embarrassing, thanks :-)