Problems using Count

[lcfjoertoft]

Hi all.

I am trying to create a search that displays 3 columns.
Analysis, Amount, Amount of Positiv results

I have nailed down Analysis, Amount, but have problems displaying Pos. result.

SELECT DISTINCT SP.ANALYSIS AS Analysis,COUNT(SP.ANALYSIS) AS Amount
FROM SAMPLE S
INNER JOIN SAMPLE_PARTS SP ON SP.SAMPLEID = S.SAMPLEID 
WHERE S.SAMPLE = '18000' 
GROUP BY SP.ANALYSIS

This gives the folowing:
Analysis | Amount
One | 30
Two | 25
Three | 30

Lets say 10 of each has the value 'POS' in the the column SP.RESULT. How would I then get a 3.rd column in my result displaying the amount of 'POS' reslts?

[adam_k]

This is an easy way to do it:

SELECT DISTINCT SP.ANALYSIS AS Analysis,COUNT(SP.ANALYSIS) AS Amount,
sum(case when SP.RESULT = 'POS' then 1 else 0 end) AS POS_Count
FROM SAMPLE S
INNER JOIN SAMPLE_PARTS SP ON SP.SAMPLEID = S.SAMPLEID 
WHERE S.SAMPLE = '18000' 
GROUP BY SP.ANALYSIS

PS: I'm using sum instead of count, but the effect is the same as it will add 1 for each POS and 0 for each value not equal to POS.

[lcfjoertoft]

@adam k: Perfect! Didn't know about case statements. Opend up a new world!

A follow up question.

Lets say I want to increase my query to contain SAMPLEID, and I want to move the analysis from the Y-axis to the X-axis giving something like this:

SampleID | One | Two | Three
a | 30 | 25 | 30
b | 10 | 0 | 0
c | 50 | 50 | 5

The analysis can also contain 'Four' but is not displayed because there are no value asociated with 'Four'.

[adam_k]

I don't really understand your data, so this might be completely wrong:

select sampleid, ONE as 'ONE', TWO as 'TWO', THREE as 'THREE',FOUR as 'FOUR' 
from (select b.* from sample a inner join sample_parts b 
on a.sampleid = b.sampleid) a 
pivot (count(analysis) for analysis in ([ONE],[TWO],[THREE],[FOUR]) ) as b

Read about PIVOT here: http://msdn.microsoft.com/en-us/library/ms177410.aspx
and here are a couple good articles: http://www.sqlprof.com/blogs/sqldev/default.aspx
http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

PS: If this wasn't helpful, please post a small sample of your data.

[lcfjoertoft]

Pivot? Didn't know that. I'm mainly using www.w3schools.com. Says nothing about pivot or case. I guess I have to broaden my search a bit.

Thanks for the reply tho :)