CODEWITHSUNDEEP
sqlcase
jason
jason

Reputation: 15

SQL CASE or not?

i am trying to make a database back/front that contains client info, the info ranges from appointments and notes etc as we are a counselling service, if you miss(dna) 3 sessions you are technically discharged from our service what i want is to automate this process- help i am a self taught newbie, i am trying to do this using "SELECT CASE" but no idea here

id | nhi    | service | type | length | contactdate | notes
1  |PEB8132 | aod     | t32  | 20     | 2012-11-20  | notes go here

This is my table when they don't attend the length becomes dna and it is this that I want to use to flag after 3 failed attempts.

Upvotes: 1

Views: 162

Answers (2)

GKV
GKV

Reputation: 501

This one works in Oracle.

select nhi ,sum(case when length='dna' then 1 else 0 end) dna_count 
from table 
group by nhi  
having sum(case when length='dna' then 1 else 0 end)>3;

Upvotes: 0

MikeSmithDev
MikeSmithDev

Reputation: 15797

You want to group together all the people who have a [length] = 'dna', showing a count of how many dna they have.

You also want to limit it to 'dna' > 3, but you can't use aggregates in WHERE clause, so we use HAVING.

You can add more columns to your SELECT statement, but you will also need to add them to the GROUP BY.

SELECT nhi, count([length]) AS dna
FROM yourTable
WHERE [length] = 'dna'
GROUP BY nhi
HAVING count([length]) > 3

If you want to return all records and not just ones where [length]='dna' then you could use a CASE.

SELECT nhi, SUM(CASE WHEN [length]='dna' THEN 1 ELSE 0 END) AS dna
FROM yourTable
GROUP BY nhi

This assumes nhi is the id of the user, which is how you identify the person.

Extra Reading: GROUP BY, HAVING

Upvotes: 1

Related Questions