SQL Grouping within Sub Queries

I am trying to group multiple columns based on a case statement.

My current code is as follows :

SELECT
    p.SCODE AS PropNumber, p.SADDR1 AS propname,  
    (CASE 
        WHEN ut.scode LIKE '%m%' THEN 'Market'
        WHEN ut.scode LIKE '%t%' THEN 'LIHTC'
        WHEN ut.scode LIKE '%p%' THEN 'LIHTC/PHA'
        WHEN ut.scode LIKE '%s%' THEN 'S8'
        ELSE 'Other'
    END) AS detail_group, 
    COUNT(ut.scode) AS UnitCount, 
    (SELECT COUNT(*) AS MoveIns
     FROM tenant t1 
     JOIN unit u1 ON t1.hunit = u1.hmy
     JOIN property p1 ON p1.hmy = u1.hproperty
     JOIN unittype ut1 ON ut1.hmy = u1.HUNITTYPE
     WHERE t1.dtmovein >= getdate() - 14 
       AND p1.scode IN ('gsaff')
       AND p1.scode  = p.scode
       AND p1.saddr1 = p.saddr1
       AND ut1.scode = ut.scode) MoveIns 
FROM
    Property p 
JOIN
    unit u ON u.hproperty = p.hmy   
JOIN 
    unittype ut ON ut.hmy = u.hunittype  
WHERE 
    p.scode IN ('gsaff') 
    AND u.exclude = 0 
    AND ut.scode IN (SELECT ut22.scode FROM unittype ut22 GROUP BY ut22.scode)  
GROUP BY  
    (CASE 
        WHEN ut.scode LIKE '%m%' THEN 'Market'
        WHEN ut.scode LIKE '%t%' THEN 'LIHTC'
        WHEN ut.scode LIKE '%p%' THEN 'LIHTC/PHA'
        WHEN ut.scode LIKE '%s%' THEN 'S8'
        ELSE 'Other'
    END), p.scode, p.SADDR1, ut.scode

The results look like this :

PropNumber  propname                detail_group    UnitCount   MoveIns
gsaff       Gardens at South Bay, LTD (gsaff)   Market             3    0
gsaff       Gardens at South Bay, LTD (gsaff)   Market             8    0
gsaff       Gardens at South Bay, LTD (gsaff)   Market             7    0
gsaff       Gardens at South Bay, LTD (gsaff)   Market             2    0
gsaff       Gardens at South Bay, LTD (gsaff)   LIHTC/PHA          3    0
gsaff       Gardens at South Bay, LTD (gsaff)   LIHTC/PHA         17    1
gsaff       Gardens at South Bay, LTD (gsaff)   LIHTC/PHA         23    3
gsaff       Gardens at South Bay, LTD (gsaff)   LIHTC/PHA         11    0
gsaff       Gardens at South Bay, LTD (gsaff)   LIHTC/PHA          2    0
gsaff       Gardens at South Bay, LTD (gsaff)   LIHTC/PHA         10    0
gsaff       Gardens at South Bay, LTD (gsaff)   LIHTC/PHA         11    0
gsaff       Gardens at South Bay, LTD (gsaff)   LIHTC/PHA          2    0

What I want is to roll up all Market and and lihtc/pha into one group so the results would look like :

  PropNumber   propname                detail_group    UnitCount   MoveIns
gsaff       Gardens at South Bay, LTD (gsaff)   Market            20    0
gsaff       Gardens at South Bay, LTD (gsaff)   LIHTC/PHA         79    4

The actual data in the unit type is m1,m2, m3 and tc1, tc2,etc so that is the cause for the multiple lines when linking back in the subquery on ut1.scode = ut.scode. I also realize i can only return one value in the subquery so am guessing i need to somehow use an exist within the where statement to accomplish this.

Answer

If the results of your query are correct, then all you need is group by on that query:

select 
  t.PropNumber, t.propname, t.detail_group, 
  sum(t.UnitCount) UnitCount, sum(t.MoveIns) MoveIns
from (
  <your query>
) t
group by 
  t.PropNumber, t.propname, t.detail_group

Advertisement

Answer