Question 256969 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – SQL sum the count of populated fields grouped by another field

How do I sum the count of populated fields.

An example of the data.

enter image description here

The desired output would be.

enter image description here

This is what I have managed so far.

select      m.project_ref,
        (   select count(*)
            from (values (m.[EWI/IWI]), (m.Glazing), (m.Solar), (m.CWI), (m.Boiler), (m.TRV), (m.LI), (m.RIRI), (m.UFI), (m.ASHP)) as v(col)
            where v.col <> ''
        ) as 'msr_cnt'
from        SMSDB1.dbo.ops_measure m

Thank you

Tags:

2

Answers


  1. 0

    This can be done by using the conditional aggregation :

    select project_ref, sum(case when EWI_IWI <> '' then 1 else 0 end )
                    + sum(case when Glazing <> '' then 1 else 0 end ) 
                    + ... as msr_count
from mytable
group by project_ref
    Login or Signup to reply.
  2. 0

    It is not entirely clear from the data provide how you are calculating msr_count, but a simple GROUP BY might be the answer here:

    SELECT
   project_ref
  ,count(*)  msr_count
 from  SMSDB1.dbo.ops_measure
 group by
   project_ref
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search