suvivor

[sgab]

i have a query with the following headings

Dr>Priority>count of mortality>alive/dead

The query basicly shows how many people of each priority died. (by doctor)

this works great

i also want to be able to show how many of each procedure are alive, how do i do this?


how do i count how many

 

[AlexCuse]

How is your alive/dead field generated in the query?

I would imagine it is count of mortality / count of patients.

If this is the case try (count of patients) - (count of mortality)



Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[sgab]

this is how i got the number of dead

SELECT aaa.Name, aaa.Priority, Count(*) AS Outcome
FROM aaa
WHERE (((aaa.thirtydaymortality)="Dead"))
GROUP BY aaa.Name, aaa.Priority;

obviously i can change the word alive for dead and get the numbers, but i want to be able to say Dr Blue (from name field) had 10 patients, 5 died 5 survived

at the moment it just tell sme 5 of his patients died, not out of how many

 

[bborissov]

SELECT aaa.Name,
       aaa.Priority,
       SUM(CASE WHEN  aaa.thirtydaymortality = 'Dead'
                THEN 1 ELSE 0 END AS Died,
       SUM(CASE WHEN  aaa.thirtydaymortality <> 'Dead'
                THEN 1 ELSE 0 END AS Survived
FROM aaa
GROUP BY aaa.Name, aaa.Priority

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Yup, Boris nailed it!

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[sgab]

i keep getting error messageges, are the commers and things in the right places, i can see open commers in your txt but no closed )

 

[bborissov]

Opps, just forgot to close parenthesis. It must be:

SELECT aaa.Name,
       aaa.Priority,
       SUM(CASE WHEN  aaa.thirtydaymortality = 'Dead'
                THEN 1 ELSE 0 END) AS Died,
       SUM(CASE WHEN  aaa.thirtydaymortality <> 'Dead'
                THEN 1 ELSE 0 END) AS Survived
FROM aaa
GROUP BY aaa.Name, aaa.Priority

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP