SQL Group By - Return results as Single Row instead of Multiple (Flatten results)

Question

I have the following job table:

job_id | is_full_time | is_short_term 
     1 |      0       |      0
     2 |      0       |      1
     3 |      1       |      0
     4 |      1       |      1

I have the following query and I'm getting results in 4 rows.

SELECT is_full_time, is_short_term, 
COUNT(CASE WHEN is_full_time = 1 AND is_short_term = 0 THEN 1 ELSE NULL END) AS FT_Long, 
COUNT(CASE WHEN is_full_time = 1 AND is_short_term = 1 THEN 1 ELSE NULL END) AS FT_Short,
COUNT(CASE WHEN is_full_time = 0 AND is_short_term = 0 THEN 1 ELSE NULL END) AS PT_Long, 
COUNT(CASE WHEN is_full_time = 0 AND is_short_term = 1 THEN 1 ELSE NULL END) AS PT_Short
FROM job
GROUP by is_full_time, is_short_term

My results look like this:

FT_Long | FT_Short | PT_Long | PT_Short
    1   0     0     0
    0   1     0     0
    0   0     1     0
    0   0     0     1

How can I combine this result into a single result row?

I want to see this:

 FT_Long | FT_Short | PT_Long | PT_Short
        1   1     1     1

Answer 1

In general this template works well for these situations:

SELECT SUM(FT_Long) as FT_Long, SUM(FT_Short) AS FT_Short, SUM(PT_Long) AS PT_Long, SUM(PT_Short) AS PT_Short
FROM
(
  SELECT is_full_time, is_short_term, 
  COUNT(CASE WHEN is_full_time = 1 AND is_short_term = 0 THEN 1 ELSE NULL END) AS FT_Long, 
  COUNT(CASE WHEN is_full_time = 1 AND is_short_term = 1 THEN 1 ELSE NULL END) AS FT_Short,
  COUNT(CASE WHEN is_full_time = 0 AND is_short_term = 0 THEN 1 ELSE NULL END) AS PT_Long, 
  COUNT(CASE WHEN is_full_time = 0 AND is_short_term = 1 THEN 1 ELSE NULL END) AS PT_Short
  FROM job
  GROUP by is_full_time, is_short_term
) T

In this case I believe you will get the same results with the following this will depend on what else is in your data that is requiring the group by in your original query

  SELECT 
  SUM(CASE WHEN is_full_time = 1 AND is_short_term = 0 THEN 1 ELSE 0 END) AS FT_Long, 
  SUM(CASE WHEN is_full_time = 1 AND is_short_term = 1 THEN 1 ELSE 0 END) AS FT_Short,
  SUM(CASE WHEN is_full_time = 0 AND is_short_term = 0 THEN 1 ELSE 0 END) AS PT_Long, 
  SUM(CASE WHEN is_full_time = 0 AND is_short_term = 1 THEN 1 ELSE 0 END) AS PT_Short
  FROM job

Answer 2

Your posted results do not match up with your select statement. Specifically, your select statement includes is_full_time and is_short_term. If you remove them from your select, then you can remove them from your group by.

SELECT
COUNT(CASE WHEN is_full_time = 1 AND is_short_term = 0 THEN 1 ELSE NULL END) AS FT_Long, 
COUNT(CASE WHEN is_full_time = 1 AND is_short_term = 1 THEN 1 ELSE NULL END) AS FT_Short,
COUNT(CASE WHEN is_full_time = 0 AND is_short_term = 0 THEN 1 ELSE NULL END) AS PT_Long, 
COUNT(CASE WHEN is_full_time = 0 AND is_short_term = 1 THEN 1 ELSE NULL END) AS PT_Short
FROM job

Answer 3

You can safely remove the GROUP BY and SUM the row instead of counting them

SELECT
    SUM(CASE WHEN is_full_time = 1 AND is_short_term = 0 THEN 1 ELSE NULL END) AS FT_Long, 
    SUM(CASE WHEN is_full_time = 1 AND is_short_term = 1 THEN 1 ELSE NULL END) AS FT_Short,
    SUM(CASE WHEN is_full_time = 0 AND is_short_term = 0 THEN 1 ELSE NULL END) AS PT_Long, 
    SUM(CASE WHEN is_full_time = 0 AND is_short_term = 1 THEN 1 ELSE NULL END) AS PT_Short
FROM job

Answer 4

Try this. The SUM() can be used to calculate the sum of the columns

SELECT is_full_time, is_short_term, 
SUM(COUNT(CASE WHEN is_full_time = 1 AND is_short_term = 0 THEN 1 ELSE NULL END)) AS FT_Long, 
SUM(COUNT(CASE WHEN is_full_time = 1 AND is_short_term = 1 THEN 1 ELSE NULL END)) AS FT_Short,
SUM(COUNT(CASE WHEN is_full_time = 0 AND is_short_term = 0 THEN 1 ELSE NULL END)) AS PT_Long, 
SUM(COUNT(CASE WHEN is_full_time = 0 AND is_short_term = 1 THEN 1 ELSE NULL END)) AS PT_Short
FROM job
GROUP by is_full_time, is_short_term