CODEWITHSUNDEEP
sqlpostgresql
iconoclast
iconoclast

Reputation: 22610

Compute value in SELECT based on whether a column is NOT NULL

I'm not sure of the correct terminology, but this should be clear when you see what I have so far.

SELECT
    (
      -- WHAT GOES HERE?
    ) as "Type",
    COUNT(*) AS pie
FROM "people"
GROUP BY "Type"
ORDER BY COUNT(*) DESC, "Type"

I'm trying to classify people based on whether or not they have a value in any of these columns:

with these types being possible:

(As you might have guessed from the SQL, this is going to generate a pie graph, so instead of putting anyone in 2 categories, I have a category that includes the people who are both employees and students.)

Upvotes: 1

Views: 28

Answers (1)

gahooa
gahooa

Reputation: 137292

I believe a CASE expression would be suitable

CASE 
    WHEN employee_id IS NOT NULL AND student_id IS NOT NULL THEN 'Both'
    WHEN employee_id IS NOT NULL then 'Employee'
    WHEN student_id IS NOT NULL then 'Student'
    ELSE 'None'
END AS "Type"

You can say GROUP BY 1 to select the first expression.

From https://www.postgresql.org/docs/10/sql-select.html ...

The optional GROUP BY clause has the general form

GROUP BY grouping_element [, ...]

GROUP BY will condense into a single row all selected rows that share the same values for the grouped expressions. An expression used inside a grouping_element can be an input column name, or the name or ordinal number of an output column (SELECT list item), or an arbitrary expression formed from input-column values. In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name.

Upvotes: 1

Related Questions