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?

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 …

## All 4 Replies

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.

commented: agree +13

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

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

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