March 4, 2009 at 2:46 am
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.
March 4, 2009 at 3:08 am
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 )
March 4, 2009 at 3:55 am
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.
March 4, 2009 at 4:33 am
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
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
March 4, 2009 at 4:56 am
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
March 4, 2009 at 5:06 am
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
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
March 4, 2009 at 5:11 am
🙂
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
March 4, 2009 at 6:54 am
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.
March 4, 2009 at 7:02 am
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
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
March 4, 2009 at 7:06 am
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.
March 4, 2009 at 7:48 am
JJSJ (3/4/2009)
So from this I would expectTotals
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?
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
March 4, 2009 at 7:56 am
You are a star!
Thanks heaps, I am eternally in your debt.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply