CODEWITHSUNDEEP
sqlsql-serversql-server-2008
etm124
etm124

Reputation: 2140

Counting rows having greater than 1 - year grouping

I currently have my query that counts patients that have a problem with an id of 16:

SELECT
    YEAR(pbd.date_created), 
    COUNT(*)
FROM
    patient_booking_data pbd inner join problems p on pbd.pid = p.pid
WHERE 
    p.problem_list_id IN (16)       
GROUP BY
    YEAR(pbd.date_created)

This groups the number of patients based on the year they entered the our system date_created.

enter image description here

I am trying to figure out how many returned within the same year.

I've tried something like this:

SELECT
    YEAR(pbd.date_created), 
    COUNT(*)
FROM
    patient_booking_data pbd inner join problems p on pbd.pid = p.pid
WHERE 
    p.problem_list_id IN (16)
    AND pbd.pid IN (
        SELECT pid FROM patient_booking_data GROUP BY pid HAVING count(*) > 1
    )
GROUP BY
    YEAR(pbd.date_created)

But that will count the pid (patient_id) if they have re-entered the system even if it is a different year.

patient_booking_data looks something like:

id   | pid   | booking_no    | date_created     | release_date
--------------------------------------------------------------------------------
1    | 565   | 12-3431       | 2012-08-10       | 2012-08-12
2    | 1231  | 12-1125       | 2012-08-11       | 2012-08-28
3    | 831   | 12-7897       | 2012-08-11       | 2012-08-11
4    | 2365  | 12-1254       | 2012-09-02       | 2012-09-03
5    | 565   | 12-5698       | 2012-10-10       | 2012-10-25

Any help would be appreciated.

Upvotes: 2

Views: 47

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

The query below will return all patients that have visited twice or more during the same year:

SELECT
    pbd.id,
    YEAR(pbd.date_created), 
    COUNT(*)
FROM
    patient_booking_data pbd inner join problems p on pbd.pid = p.pid
WHERE 
    p.problem_list_id IN (16)       
GROUP BY
    YEAR(pbd.date_created), pbd.id
HAVING COUNT(*) > 1

You can count the number of such patients per year by doing another group by on a derived table

SELECT COUNT(*), y FROM (
    SELECT
        pbd.id,
        YEAR(pbd.date_created) y
    FROM
        patient_booking_data pbd inner join problems p on pbd.pid = p.pid
    WHERE 
        p.problem_list_id IN (16)       
    GROUP BY
        YEAR(pbd.date_created), pbd.id
    HAVING COUNT(*) > 1
) t1 GROUP BY y

Upvotes: 1

Related Questions