CODEWITHSUNDEEP
sqlsql-servercount
Jana
Jana

Reputation: 61

Need to count two separate values in one column

I'm trying to get the sums of two separate values to appear in one row, per unique ID. I've tried a couple routes but still can't get everything to appear in one row. I've tried a few different ways of using case statements and I've tried using OVER with partitions, as well, to no avail.

Here is my query so far:

select distinct ID_NUM, 
  sum(case when isnull(sch.SUBTERM_CDE,0) like '%N' then 1 else 0 end) as 'total_n',
  sum(case when isnull(sch.SUBTERM_CDE,0) like '%T' then 1 else 0 end) as 'total_t'
from STUDENT_CRS_HIST sch
where sch.END_DTE > GETDATE()
group by sch.ID_NUM, sch.SUBTERM_CDE
order by ID_NUM

Upvotes: 0

Views: 113

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Change the group by to remove SUBTERM_CDE:

select ID_NUM, 
       sum(case when sch.SUBTERM_CDE like '%N' then 1 else 0 end) as total_n,
       sum(case when sch.SUBTERM_CDE like '%T' then 1 else 0 end) as total_t
from STUDENT_CRS_HIST sch
where sch.END_DTE > GETDATE()
group by sch.ID_NUM
order by ID_NUM;

In addition:

  • The distinct is unneeded and misleading.
  • Column aliases should not be enclosed in single quotes (only use single quotes for string and date constants).
  • The text for NULL is unnecessary -- and misleading. The alternative value is an integer, but the field is supposedly a string.

Upvotes: 1

Lamak
Lamak

Reputation: 70638

You are almost right, but you are grouping by an extra column:

SELECT  ID_NUM, 
        SUM(CASE WHEN sch.SUBTERM_CDE LIKE '%N' THEN 1 ELSE 0 END) AS total_n,
        SUM(CASE WHEN sch.SUBTERM_CDE LIKE '%T' THEN 1 ELSE 0 END) AS total_t
FROM STUDENT_CRS_HIST sch
WHERE sch.END_DTE > GETDATE()
GROUP BY sch.ID_NUM -- you shouldn't group by SUBTERM_CDE
ORDER BY ID_NUM

Upvotes: 2

Related Questions