Summary of Returns by Line

1

Hi Team, I am looking the short way to generate a query to count and sum of fields with nom-zero value, from a table named RETURN with the following structure:
Return_Id, Fld_001, Fld_002, Fld_003,.., Fld_200 (All Fld's are variables currency type)

Result:
FldName Qty Sum
======= =============== ===========
Fld_001 Count(Fld_001 > 0) Sum(Fld_001)
Fld_002 Count(Fld_001 > 0) Sum(Fld_001)
……….
Fld_200 Count(Fld_200 > 0) Sum(Fld_200)

Basically, I want to avoid manual encoding of a long query.
I appreciate your help, ALF.

2

Welcome

Please provide sample data in the following form for example

Create table #sample(id int, name varchar(50))

Insert into #sample
Select 1, :bolts'

Etc

3

use case statement for count > 1

sum(case when Fld_001 > 0 then 1 else 0 end)

4

Example:
CREATE TABLE DOC (
DCID INT NOT NULL, FLD1 INT NOT NULL, FLD2 INT NOT NULL,
FLD3 INT NOT NULL, FLD4 INT NOT NULL);
INSERT INTO DOC VALUES
(1,100,500,0,100),(2,0,0,350,0),
(3,0,0,100,200),(4,150,300,200,0),
(5,0,200,300,300),(6,200,0,150,0),
(7,0,400,300,400),(8,0,0,600,0),
(9,0,0,0,500),(10,0,250,70,0);

Result for tbl DOC :
FLD QTY SUMM
FLD1 3 450
FLD2 5 1650
FLD3 8 2070
FLD4 5 1500

I could use (as Mike said):

SELECT 'FLD1' FLD, SUM(CASE WHEN FLD1 > 0 THEN 1 ELSE 0 END) QTY, SUM(FLD1) SUMM
FROM DOC
UNION ALL
SELECT 'FLD2', SUM(CASE WHEN FLD2 > 0 THEN 1 ELSE 0 END), SUM(FLD2)
FROM DOC
UNION ALL
SELECT 'FLD3', SUM(CASE WHEN FLD3 > 0 THEN 1 ELSE 0 END), SUM(FLD3)
FROM DOC
UNION ALL
SELECT 'FLD4', SUM(CASE WHEN FLD4 > 0 THEN 1 ELSE 0 END), SUM(FLD4)
FROM DOC

The real tbl DOC has over 200 fields. The query would be huge and inefficient.

Any idea?
Regards, ALF.

5

Hi @ALF

You May try this

;with cte AS(
SELECT * FROM #DOC
UNPIVOT(
	Value 
	FOR FLD IN(FLD1, FLD2, FLD3, FLD4)
) as Unpvt
)
SELECT FLD, 
SUM(
	CASE WHEN Value = 0 THEN 0 ELSE 1 END
) AS Qty, SUM(Value) AS Total FROM cte
GROUP BY FLD

Result:
image

Regards,
Jacky

6

Thanks, Yosiasz, Mike & Jacky. Safe my day. I will generate a script including your query for several doc types. Will keep in touch.