asked on

Some TSQL

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

ralmada

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]

ralmada

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]

ralmada

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 Pletcher

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

ralmada

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

ralmada

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

soozh

ASKER

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.

ralmada

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 Pletcher

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 :-) .

ralmada

>>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 Pletcher

>> 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

ralmada

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

soozh

ASKER

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

How can i round to the nearest 0.1 ?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

--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 Pletcher

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.

SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial