CODEWITHSUNDEEP
sql
b00kgrrl
b00kgrrl

Reputation: 597

How to create an alias for a filtered column in a SQL query

The SQL query below is giving me the desired results, but the use of CASE statements seems incorrect. Is there another way to create an alias for a filtered column? Is there a more efficient way to achieve the same results?

Here is the current query:

SELECT 
      year, 
      college_name, 
      count(id_999999999) as count_id_999999999,
      count(id_999999999)/count(student_id) as percent_id_999999999,
      count(id_other) as count_id_other,
      count(id_other)/count(student_id) as percent_id_other,
      count(student_id) as total_id

FROM      (SELECT fiscal_year, semester, 

                 CASE
                     WHEN student_id IN ('999999999') THEN 'id - 999999999' 
                 END
                 AS id_999999999,

                 CASE
                     WHEN student_id NOT IN ('999999999') THEN 'id - Other'
                 END
                 AS id_other,

                student_id

            FROM enrolment_data) enrol

GROUP BY    year, college_name
ORDER BY    year, college_name;

And here are the desired results:

enter image description here

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

The query is overly complicated. You don't need a subquery. I would write the query as:

SELECT year, college_name, 
       sum(case when student_id IN ('999999999') then 1 else 0 end) as count_id_999999999,
       avg(case when student_id IN ('999999999') then 1.0 else 0 end) as percent_id_999999999,
       sum(case when student_id NOT IN ('999999999') then 1 else 0 end) as count_id_other,
       avg(case when student_id NOT IN ('999999999') then 1.0 else 0 end) as percent_id_other
       count(*) as total_id
FROM  enrolment_data ed
GROUP BY year, college_name
ORDER BY year, college_name;

Upvotes: 1

Related Questions