We help IT Professionals succeed at work.

Some TSQL

Medium Priority
242 Views
Last Modified: 2012-05-12
SQL Server 2008!

I have a table that contains the following columns and data:

Date               Gender        Value
2010-01-15   M                   0
2010-01-15   M                  10
2010-01-15   M                  12
2010-01-15   F                  0
2010-01-15   F                  8
2010-01-15   F                  0
2010-02-15   M                  6
2010-02-15   M                  9
2010-02-15   M                  6
2010-02-15   F                  0
2010-02-15   F                  6
2010-03-15   M                  0

The table has a date column, a column for the patients gender, and a measurement.

I need a query that returns the following for each date
a) the % of patients that have a measurement greater than zero
b) the % of male patients that have a measurement greater than zero
c) the % of female patients that have a measurement greater than zero

So for the above data i think i should get

2010-01-15   50%   67%   33%
2010-02-15   80%   100%  50%
2010-03-15   0%      0%       0%

/thanks
Comment
Watch Question

CERTIFIED EXPERT

Commented:
select       [Date],
      sum(case when Value > 0 then 1 else 0 end) / count(Value) as greate_zero,
      sum(case when gender = 'M' then 1 else 0 end) / count(Value) as male,
      sum(case when gender = 'F' then 1 else 0 end) / count(Value) as male
from yourtable
group by [Date]
CERTIFIED EXPERT

Commented:
wrong column name, it should be:


select       [Date],
      sum(case when Value > 0 then 1 else 0 end) / count(Value) as greate_zero,
      sum(case when gender = 'M' then 1 else 0 end) / count(Value) as male,
      sum(case when gender = 'F' then 1 else 0 end) / count(Value) as female
from yourtable
group by [Date]
CERTIFIED EXPERT

Commented:
actually it should be:

select       [Date],
      sum(case when Value > 0 then 1 else 0 end) * 100.0 / count(Value) as greate_zero,
      sum(case when gender = 'M' then 1 else 0 end) * 100.0 / count(Value) as male,
      sum(case when gender = 'F' then 1 else 0 end) *100.0 / count(Value) as female
from yourtable
group by [Date]
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

SELECT 
	date,
    CAST(SUM(CASE WHEN value > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(value) AS int) AS [%Patients],
    CAST(SUM(CASE WHEN value > 0 AND gender = 'M' THEN 1 ELSE 0 END) * 100.0 / COUNT(value) AS int) AS [%Male],
    CAST(SUM(CASE WHEN value > 0 AND gender = 'F' THEN 1 ELSE 0 END) * 100.0 / COUNT(value) AS int) AS [%Female]
FROM tablename
GROUP BY date
ORDER BY date

Open in new window

CERTIFIED EXPERT

Commented:
well, yeah I missed the value part, but seems that Stoch caught it.

Now whether you want to convert it to int to round it, that's just formatting.

Finally, remember that Date is a reserved word in SQL 2008, so it's recommended to put it between brackets when referring to a column
select       [Date],
      sum(case when Value > 0 then 1 else 0 end) * 100.0 / count(Value) as greate_zero,
      sum(case when Value > 0 gender = 'M' then 1 else 0 end) * 100.0 / count(Value) as male,
      sum(case when Value > 0 gender = 'F' then 1 else 0 end) *100.0 / count(Value) as female
from yourtable
group by [Date]

Open in new window

CERTIFIED EXPERT

Commented:
looks like today is not my day


select       [Date],
      sum(case when Value > 0 then 1 else 0 end) * 100.0 / count(Value) as greate_zero,
      sum(case when Value > 0 and gender = 'M' then 1 else 0 end) * 100.0 / count(Value) as male,
      sum(case when Value > 0 and gender = 'F' then 1 else 0 end) * 100.0 / count(Value) as female
from yourtable
group by [Date]

Open in new window

soozhCEO

Author

Commented:
I think that all these solutions give the percentage of males against all patients whilst I am interested in the percentage of male patients against male patients only.
CERTIFIED EXPERT

Commented:
u mean like this?

select       [Date],
      sum(case when Value > 0 then 1 else 0 end) * 100.0 / count(Value) as greate_zero,
      sum(case when Value > 0 and gender = 'M' then 1 else 0 end) * 100.0 / sum(case when gender = 'M' then 1 else 0 end) as male,
      sum(case when Value > 0 and gender = 'F' then 1 else 0 end) * 100.0 / sum(case when gender = 'F' then 1 else 0 end) as female
from yourtable
group by [Date]
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
I was basing the CAST to int on two things:
1) This part of the original q:
So for the above data i think i should get
2010-01-15   50%   67%   33%
2010-02-15   80%   100%  50%
2010-03-15   0%      0%       0%
2) The actual result w/o CASTing/rounding has too many digits to read easily:
select 7 * 100.0 / 23 --i.e. > 0 count was 7, total count was 23 would show as:
30.434782
Ugh.  Much better for it to just be:
30
as the percent :-) .
CERTIFIED EXPERT

Commented:
>>I was basing the CAST to int on two things:<< Fair enough :)




select       [Date],
      cast(sum(case when Value > 0 then 1 else 0 end) * 100.0 / count(Value) as int) as greate_zero,
      cast(sum(case when Value > 0 and gender = 'M' then 1 else 0 end) * 100.0 / sum(case when gender = 'M' then 1 else 0 end) as int) as male,
      cast(sum(case when Value > 0 and gender = 'F' then 1 else 0 end) * 100.0 / sum(case when gender = 'F' then 1 else 0 end) as int) as female
from yourtable
group by [Date]

Open in new window

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>> I think that all these solutions give the percentage of males against all patients whilst I am interested in the percentage of male patients against male patients only. <<

Good point.
SELECT 
    date,
    CAST(SUM(CASE WHEN value > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(value) AS int) AS [%Patients],
    CAST(SUM(CASE WHEN value > 0 AND gender = 'M' THEN 1 ELSE 0 END) * 100.0 / 
        CASE WHEN SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) = 0 THEN 1 ELSE
        SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) END AS int) AS [%Male],
    CAST(SUM(CASE WHEN value > 0 AND gender = 'F' THEN 1 ELSE 0 END) * 100.0 /
        CASE WHEN SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) = 0 THEN 1 ELSE
        SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) END AS int) AS [%Female]
FROM tablename
GROUP BY date
ORDER BY date

Open in new window

CERTIFIED EXPERT

Commented:
Another way to avoid a potential division by zero error:
select       [Date],
      cast(sum(case when Value > 0 then 1 else 0 end) * 100.0 / count(Value) as int) as greate_zero,
      cast(isnull(sum(case when Value > 0 and gender = 'M' then 1 else 0 end) * 100.0 / nullif(sum(case when gender = 'M' then 1 else 0 end), 0), 0) as int) as male,
      cast(isnull(sum(case when Value > 0 and gender = 'F' then 1 else 0 end) * 100.0 / nullif(sum(case when gender = 'F' then 1 else 0 end), 0), 0) as int) as female
from yourtable
group by [Date]

Open in new window

soozhCEO

Author

Commented:
ok... it appears that it truncates the percentage value calculated.. so 76.89 becomes 76.

How can i round to the nearest 0.1 ?
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Hmm, I really thought CAST *rounds* to the type indicated.

If you want one decimal place, CAST to decimal(4, 1).

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
--like so:
SELECT
    date,
    CAST(SUM(CASE WHEN value > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(value) AS decimal(4, 1)) AS [%Patients], ...
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
But you're right, with that CAST SQL is truncating.

A CAST to decimal should definitely round, so CAST to decimal(4, 1) or decimal(3, 0) depending on the output you want.
CERTIFIED EXPERT
Commented:
I knew casting by int was not the best choice :), aside from decimal you can also use round to zero.

round(.....  expression ...., 0)
select       [Date],
      round(sum(case when Value > 0 then 1 else 0 end) * 100.0 / count(Value),  0) as greate_zero,
      round(isnull(sum(case when Value > 0 and gender = 'M' then 1 else 0 end) * 100.0 / nullif(sum(case when gender = 'M' then 1 else 0 end), 0), 0), 0) as male,
      round(isnull(sum(case when Value > 0 and gender = 'F' then 1 else 0 end) * 100.0 / nullif(sum(case when gender = 'F' then 1 else 0 end), 0), 0), 0) as female
from yourtable
group by [Date]

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.