Count on multiple columns excluding ones containing zero

Question

Post reply

Count on multiple columns excluding ones containing zero

JJSJ

SSC Rookie

Points: 45
More actions
March 4, 2009 at 2:46 am

#126842

Hi there,
I require a count on multiple columns but excluding the ones containing zeros.
Heres my code.
*********************
update table1
set [Count Percentage] =
(select count (*)
FROM
(SELECT DISTINCT
[Documentation Percentage],
[Risk assessment Percentage],
[Care plans Percentage],
[Further assessment Percentage],
[Bed rail assessment Percentage]
from table1)
table1)
**********************
This gives me a row count of 4 but two of the rows have a zero in and I would like these not to be counted.
Does anyone know what the code would be to get this result?
Any help would be greatly appreciated.
Thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

dharani-919169 Old Hand Points: 302 More actions · Answer 1

hi,

this may help u

but this works such as if one of the column in a row is a zero , then it will not count that row

update table1

set [Count Percentage] =

(select count (*)

FROM

(SELECT DISTINCT

[Documentation Percentage],

[Risk assessment Percentage],

[Care plans Percentage],

[Further assessment Percentage],

[Bed rail assessment Percentage]

from table1 where [Documentation Percentage]<>0 or [Risk assessment Percentage] <>0

[Care plans Percentage]<>0 or [Further assessment Percentage] <>0

[Bed rail assessment Percentage] <>0 )

JJSJ SSC Rookie Points: 45 More actions · Answer 2

Hi thanks for that but it is still giving me a count of 4 where I know there should be at least one row showing a count of 2.

Thanks.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Try this. The DISTINCT is replaced by a GROUP BY which for this example does the same thing but is more deliberate. Run the inner select to see what it returns, it shows the rows preselected for the count and also the number of dupes of each row. If you're confident that the inner select works ok, then remove the column expressions which you don't need - in fact, you could replace the whole column list of the inner select with SELECT 1 FROM...

DROP TABLE #table1

CREATE TABLE #table1 (

RowID int IDENTITY(1,1),

[Documentation Percentage] numeric(5,2),

[Risk assessment Percentage] numeric(5,2),

[Care plans Percentage] numeric(5,2),

[Further assessment Percentage] numeric(5,2),

[Bed rail assessment Percentage] numeric(5,2))

INSERT INTO #table1 (

[Documentation Percentage],

[Risk assessment Percentage],

[Care plans Percentage],

[Further assessment Percentage],

[Bed rail assessment Percentage])

SELECT 10.1, 11.1, 12.1, 13.1, 14.1 UNION ALL

SELECT 10.1, 11.1, 12.1, 13.1, 14.1 UNION ALL -- dupe

SELECT 20.1, 0, 22.1, 23.1, 24.1 UNION ALL -- omit

SELECT 30.1, 31.1, 32.1, 33.1, 34.1 UNION ALL

SELECT 30.1, 31.1, 32.1, 33.1, 34.1 UNION ALL -- dupe

SELECT 40.1, 41.1, 0, 43.1, 44.1 -- omit

SELECT COUNT(*)

FROM (

SELECT 'Y' AS OneValidRow,

COUNT(*) AS [Distinct rows], -- include for testing

[Documentation Percentage], -- include for testing

[Risk assessment Percentage], -- include for testing

[Care plans Percentage], -- include for testing

[Further assessment Percentage], -- include for testing

[Bed rail assessment Percentage] -- include for testing

FROM #table1

WHERE [Documentation Percentage] <> 0

AND [Risk assessment Percentage] <> 0

AND [Care plans Percentage] <> 0

AND [Further assessment Percentage] <> 0

AND [Bed rail assessment Percentage] <> 0

GROUP BY [Documentation Percentage],

[Risk assessment Percentage],

[Care plans Percentage],

[Further assessment Percentage],

[Bed rail assessment Percentage]

) d

-- Two rows returned

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

JohnSmith001 Right there with Babe Points: 765 More actions · Answer 4

One more way, assuming that all columns you are considering are of numeric data type.

DECLARE @T Table (Id INT, COL1 INT, COL2 INT,Val int)

INSERT INTO @T SELECT

1,2,3,10 UNION ALL SELECT

1,2,0,3 UNION ALL SELECT

2,0,3,10 UNION ALL SELECT

2,2,0,10

SELECT COUNT(NULLIF(ID*COL1*COL2*VAL,0)) as You_want

FROM @T

John Smith

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Mangal Pardeshi (3/4/2009)
One more way, assuming that all columns you are considering are of numeric data type.
DECLARE @T Table (Id INT, COL1 INT, COL2 INT,Val int)
INSERT INTO @T SELECT
1,2,3,10 UNION ALL SELECT
1,2,0,3 UNION ALL SELECT
2,0,3,10 UNION ALL SELECT
2,2,0,10
SELECT COUNT(NULLIF(ID*COL1*COL2*VAL,0)) as You_want
FROM @T

This doesn't work, it counts dupes:

DECLARE @T Table (Id INT, COL1 INT, COL2 INT,Val int)

INSERT INTO @T SELECT

1,2,3,10 UNION ALL SELECT -- count

1,2,3,10 UNION ALL SELECT

1,2,3,11 UNION ALL SELECT -- count

1,2,0,3 UNION ALL SELECT

2,0,3,10 UNION ALL SELECT

2,2,0,10

SELECT COUNT(NULLIF(ID*COL1*COL2*VAL,0)) as You_want

FROM @T

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

JohnSmith001 Right there with Babe Points: 765 More actions · Answer 6

🙂

DECLARE @T Table (Id INT, COL1 INT, COL2 INT,Val int)

INSERT INTO @T SELECT

1,2,3,10 UNION ALL SELECT -- count

1,2,3,10 UNION ALL SELECT

1,2,3,11 UNION ALL SELECT -- count

1,2,0,3 UNION ALL SELECT

2,0,3,10 UNION ALL SELECT

2,2,0,10

SELECT COUNT(NULLIF(ID*COL1*COL2*VAL,0)) as You_want

FROM (

SELECT DISTINCT ID,COL1,COL2,VAL

FROM

@T)T

John Smith

JJSJ SSC Rookie Points: 45 More actions · Answer 7

Hi,

I don't think I have been clear in what I require. I will use your example.

DECLARE @T Table (Id INT, COL1 INT, COL2 INT,Val int)

INSERT INTO @T SELECT

1,2,3,10 UNION ALL SELECT -- count

1,2,3,10 UNION ALL SELECT

1,2,3,11 UNION ALL SELECT -- count

1,2,0,3 UNION ALL SELECT

2,0,3,10 UNION ALL SELECT

2,2,0,10

SELECT COUNT(NULLIF(ID*COL1*COL2*VAL,0)) as You_want

FROM (

SELECT DISTINCT ID,COL1,COL2,VAL

FROM

@T)T

The results of the above tell me theres two rows with a zero

I need to know how many columns have been populated with anything other than a zero.

Say theres 3 rows and 5 columns

row1 - 3 columns populated (this one has 2 zeros)

row2 - 5 columns populated (this one has no zeros)

row3 - 4 columns populated (this one has 1 zero)

Sorry I hope this makes sense???

Thanks.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

JJSJ (3/4/2009)
Hi,
I don't think I have been clear in what I require. I will use your example.
.
.
.
Sorry I hope this makes sense???

Not yet. Can I suggest you use the following as sample data because the columns more closely match your requirements? Using the sample, what would you expect as your output?

DROP TABLE #table1

CREATE TABLE #table1 (

RowID int IDENTITY(1,1),

[Documentation Percentage] numeric(5,2),

[Risk assessment Percentage] numeric(5,2),

[Care plans Percentage] numeric(5,2),

[Further assessment Percentage] numeric(5,2),

[Bed rail assessment Percentage] numeric(5,2))

INSERT INTO #table1 (

[Documentation Percentage],

[Risk assessment Percentage],

[Care plans Percentage],

[Further assessment Percentage],

[Bed rail assessment Percentage])

SELECT 10.1, 11.1, 12.1, 13.1, 14.1 UNION ALL

SELECT 10.1, 11.1, 12.1, 13.1, 14.1 UNION ALL -- dupe

SELECT 20.1, 0, 22.1, 23.1, 24.1 UNION ALL -- omit

SELECT 30.1, 31.1, 32.1, 33.1, 34.1 UNION ALL

SELECT 30.1, 31.1, 32.1, 33.1, 34.1 UNION ALL -- dupe

SELECT 40.1, 41.1, 0, 43.1, 44.1 -- omit

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

JJSJ SSC Rookie Points: 45 More actions · Answer 9

So from this I would expect

Totals

5

4

5

4

As these are the totals of columns populated with figures other than zero.

SELECT 10.1, 11.1, 12.1, 13.1, 14.1 UNION ALL

SELECT 10.1, 11.1, 12.1, 13.1, 14.1 UNION ALL -- dupe

SELECT 20.1, 0, 22.1, 23.1, 24.1 UNION ALL -- omit

SELECT 30.1, 31.1, 32.1, 33.1, 34.1 UNION ALL

SELECT 30.1, 31.1, 32.1, 33.1, 34.1 UNION ALL -- dupe

SELECT 40.1, 41.1, 0, 43.1, 44.1 -- omit

Thanks heaps.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

JJSJ (3/4/2009)
So from this I would expect
Totals
5
5
4
5
5
4
As these are the totals of columns populated with figures other than zero.
SELECT 10.1, 11.1, 12.1, 13.1, 14.1 UNION ALL
SELECT 10.1, 11.1, 12.1, 13.1, 14.1 UNION ALL -- dupe
SELECT 20.1, 0, 22.1, 23.1, 24.1 UNION ALL -- omit
SELECT 30.1, 31.1, 32.1, 33.1, 34.1 UNION ALL
SELECT 30.1, 31.1, 32.1, 33.1, 34.1 UNION ALL -- dupe
SELECT 40.1, 41.1, 0, 43.1, 44.1 -- omit
Thanks heaps.

SELECT

CASE WHEN [Documentation Percentage] > 0 THEN 1 ELSE 0 END +

CASE WHEN [Risk assessment Percentage] > 0 THEN 1 ELSE 0 END +

CASE WHEN [Care plans Percentage] > 0 THEN 1 ELSE 0 END +

CASE WHEN [Further assessment Percentage] > 0 THEN 1 ELSE 0 END +

CASE WHEN [Bed rail assessment Percentage] > 0 THEN 1 ELSE 0 END

FROM #table1

What's next?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

JJSJ SSC Rookie Points: 45 More actions · Answer 11

You are a star!

Thanks heaps, I am eternally in your debt.