TSQL QUERY HELP

Question

Post reply

TSQL QUERY HELP

PearlJammer1

SSCrazy Eights

Points: 8477
More actions
September 4, 2013 at 8:15 am

#297412

Can anybody help with the following query:
Basically its returning 100% when all records are populated and 0% when only some are populated !
select coll,
count(case when coll is not null and value is not null then 1 end) CntCol,
count(case when coll is not null and value is not null
then 1 end) / count(coll)*100.0 Percentage
from
(
select 'AltPhone' coll, AltPhone value
from ABC_User_CustomProfile
union all
select 'lastname' coll, lastname value
from ABC_User_CustomProfile
union all
select 'staffid' coll, staffid value
from ABC_User_CustomProfile
) src
group by coll

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

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

Try it like this:

select coll,

count(case when coll is not null and value is not null then 1 else 0 end) CntCol,

count(case when coll is not null and value is not null

then 1 else 0 end) / count(*)*100.0 Percentage

from

...

The probability of survival is inversely proportional to the angle of arrival.

PearlJammer1 SSCrazy Eights Points: 8477 More actions · Answer 2

Tried that and it it returns 100% for all 3 columns....i need it to reflect the correct percentage ?

Any other ideas ?

sturner SSC-Insane Points: 22411 More actions · Answer 3

My bad... your count()'s should be sum()'s (I missed that the first time):

select coll,

sum(case when coll is not null and value is not null then 1 else 0 end) CntCol,

sum(case when coll is not null and value is not null

then 1 else 0 end) / count(*)*100.0 Percentage

from

...

The probability of survival is inversely proportional to the angle of arrival.

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

You are seeing this because of integer division. When your count is 100% it works because the integer division returns 1. When it is less than 100% your first calculation will always be 0.

Suppose you have 100 rows and 99 of them should be counted. You are in affect doing 99/100 as the first part of your calculation which will ALWAYS be zero. You need to force the numerator to be a numeric.

Here is one way you can do that easily.

select coll,

sum(case when coll is not null and value is not null then 1 end) CntCol,

sum(case when coll is not null and value is not null

then 1 end) / (count(coll) * 1.0) * 100.0 Percentage

from

(

select 'AltPhone' coll, AltPhone value

from ABC_User_CustomProfile

union all

select 'lastname' coll, lastname value

from ABC_User_CustomProfile

union all

select 'staffid' coll, staffid value

from ABC_User_CustomProfile

) src

group by coll

--edit--

typo

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

sturner (9/4/2013)
Try it like this:
select coll,
count(case when coll is not null and value is not null then 1 else 0 end) CntCol,
count(case when coll is not null and value is not null
then 1 else 0 end) / count(*)*100.0 Percentage
from
...

Start simple:

SELECT

[AltPhone] = COUNT(AltPhone),

[lastname] = COUNT(lastname),

[staffid] = COUNT(staffid)

FROM ABC_User_CustomProfile

^{“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

PearlJammer1 SSCrazy Eights Points: 8477 More actions · Answer 6

Thanks for the quick reply, but its now back to showing 0% for rows that have some data and 100% for the field that is fully populated.

Example there are 5000 rows

coll CntCol Percentage

altphone 2000 0.0

lastname 3900 0.0

staffid 5000 100.0

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

PearlJammer1 (9/4/2013)
Thanks for the quick reply, but its now back to showing 0% for rows that have some data and 100% for the field that is fully populated.
Example there are 5000 rows
coll CntCol Percentage
altphone 2000 0.0
lastname 3900 0.0
staffid 5000 100.0

When you post the results of a query, particularly the anecdotal results, post the query too so folks don't have to fish around trying to figure out what you've just run.

^{“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

PearlJammer1 SSCrazy Eights Points: 8477 More actions · Answer 8

Thank you that query has worked and your explanation makes perfect sense!!!

🙂

PearlJammer1 SSCrazy Eights Points: 8477 More actions · Answer 9

Yeah sorry about the messy query results. I did preview it and it looked ok but when i posted it got out of line.

Incidentally how do you copy a result set into here ? Is there a particular IFCode shortcut i should use ?

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

PearlJammer1 (9/4/2013)
Yeah sorry about the messy query results. I did preview it and it looked ok but when i posted it got out of line.
Incidentally how do you copy a result set into here ? Is there a particular IFCode shortcut i should use ?

Yep, it's to the left of the editing window, and it's [co de="sql"][/co de] - remove the spaces in the word 'code'.

Like this:

;WITH ABC_User_CustomProfile (AltPhone, lastname, staffid) AS (

SELECT '07787 345345', 'smith', '001' UNION ALL

SELECT '07787 345344', 'jones', NULL UNION ALL

SELECT '07787 345343', 'patel', NULL UNION ALL

SELECT '07787 345342', 'williams', '004' UNION ALL

SELECT NULL, 'sahathevarajan', '005' UNION ALL

SELECT '07787 345341', 'guamundsdottir', '006'

)

SELECT cav.col1, cav.CntCol, d.AllRows

FROM (

SELECT

[AltPhone] = COUNT(AltPhone),

[lastname] = COUNT(lastname),

[staffid] = COUNT(staffid),

[AllRows] = COUNT(*)

FROM ABC_User_CustomProfile

) d

CROSS APPLY (VALUES

('AltPhone',[AltPhone]),

('lastname',[lastname]),

('staffid',[staffid])

) cav (col1,CntCol)

^{“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

PearlJammer1 SSCrazy Eights Points: 8477 More actions · Answer 11

yeah i can post code without a problem using that method - as i did in this very post. But how do i post the results of the code that get returned in grid format in ssms ?

Ive tried all the shortcuts and non of them seem to align the results correctly ?