Search code examples
sql-servert-sql

How to calculate Count of Columns that greater than zero

I need a T-SQL query that calculates columns greater than zero value.

Sample image is posted below. 13

I haved tried below query but total count showing wrong.

select 
   Site, cu_id, 
   count(distinct case when divi='EX_TO1' then inv_no end)EX_TO1, 
   count(distinct case when divi='EX_TO2' then inv_no end)EX_TO2, 
   count(distinct case when divi='EX_TO3' then inv_no end)EX_TO3, 
   count(distinct case when divi in ('EX_TO4','EX_TO9') then inv_no end)EX_TO4, 
   count(distinct case when divi='EX_TO5' then inv_no end)EX_TO5, 
   count(distinct case when divi='EX_TO6' then inv_no end)EX_TO6, 
   count(distinct case when divi='EX_TO7' then inv_no end)EX_TO7, 
   count(distinct case when divi='EX_TO8' then inv_no end)EX_TO8, 
   count(distinct inv_no)total 
from OSH 
group by Site,cu_id 
order by  Site,cu_id asc`

sample data

Solution

  • A minimal reproducible example is not provided.

    Please try the following conceptual solution that is using SQL Server's XML and XQuery.

    It is generic, i.e. will work for any number of columns matching EXT_T* naming convention.

    Notable points:

    • CROSS APPLY is producing XML for each row.
    • XQuery .value() method is using XPath expression to calculate how many columns in question having a value greater than zero.

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (
    site varchar(10), 
    cu_id INT, 
    EXT_T01 INT, 
    EXT_T02 INT, 
    EXT_T03 INT, 
    EXT_T04 INT
);
INSERT @tbl (site, cu_id, EXT_T01, EXT_T02, EXT_T03, EXT_T04) VALUES
('SI_BMD_TC', 1730418, 0, 2, 0, 1),
('SI_BMD_TC', 1160418, 0, 0, 0, 0),
('SI_BMD_TC', 4777418, 1, 2, 3, 4);
-- DDL and sample data population, end

SELECT *
    , x.value('count(/root/*[substring(local-name(),1,5)="EXT_T"][text()[1] gt 0])', 'INT') AS [counter]
FROM @tbl AS t
CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(x);

    Output

    site cu_id EXT_T01 EXT_T02 EXT_T03 EXT_T04 counter
    SI_BMD_TC 1730418 0 2 0 1 2
    SI_BMD_TC 1160418 0 0 0 0 0
    SI_BMD_TC 4777418 1 2 3 4 4