Get proper count of the rows from table

Question

Here is my query:

SELECT  DISTINCT
        Patient_Ref_master.Dept_ID AS 'Dept_ID' ,
        ( SELECT DISTINCT
                    COUNT(Patient_Ref_master.Sr_No)
          FROM      Patient_Ref_master
                    LEFT JOIN Patient_Master ON Patient_Master.Pat_Code = Patient_Ref_master.Pat_ID
          WHERE     Patient_Ref_master.creation_Date = '2013/08/02'
                    AND Patient_Master.Age >= 0
                    AND Patient_Master.Age <= 16
                    AND Patient_Master.Pat_Sex = 1
        ) AS 'Boys' ,
        ( SELECT DISTINCT
                    COUNT(Patient_Ref_master.Sr_No)
          FROM      Patient_Ref_master
                    LEFT JOIN Patient_Master ON Patient_Master.Pat_Code = Patient_Ref_master.Pat_ID
          WHERE     Patient_Ref_master.creation_Date = '2013/08/02'
                    AND Patient_Master.Age >= 0
                    AND Patient_Master.Age <= 16
                    AND Patient_Master.Pat_Sex = 2
        ) AS 'Girls' ,
        ( SELECT DISTINCT
                    COUNT(Patient_Ref_master.Sr_No)
          FROM      Patient_Ref_master
                    LEFT JOIN Patient_Master ON Patient_Master.Pat_Code = Patient_Ref_master.Pat_ID
          WHERE     Patient_Ref_master.creation_Date = '2013/08/02'
                    AND Patient_Master.Age > 16
                    AND Patient_Master.Pat_Sex = 2
        ) AS 'Females' ,
        ( SELECT DISTINCT
                    COUNT(Patient_Ref_master.Sr_No)
          FROM      Patient_Ref_master
                    LEFT JOIN Patient_Master ON Patient_Master.Pat_Code = Patient_Ref_master.Pat_ID
          WHERE     Patient_Ref_master.creation_Date = '2013/08/02'
                    AND Patient_Master.Age > 16
                    AND Patient_Master.Pat_Sex = 1
        ) AS 'Males'
FROM    Patient_Ref_master
        LEFT JOIN Patient_Master ON Patient_Master.Pat_Code = Patient_Ref_master.Pat_ID
        LEFT JOIN Gender_master ON Gender_master.Code = Patient_Master.Pat_Sex
        LEFT JOIN Dept_Master ON Dept_Master.Dept_code = Patient_Ref_master.Dept_ID
WHERE   Patient_Ref_master.creation_Date = '2013/08/02'
GROUP BY Patient_Master.Pat_Sex ,
        Patient_Ref_master.Dept_ID

Here I am trying to achieve the count of boys, girls, males and females according to departmet wise that is selected as first column. But I am getting result as

Dept_ID Boys    Girls   Females Males
102     3       0       11      6
103     3       0       11      6
104     3       0       11      6

My total count of the patients is 20 which is getting reflected in every row. How can I achieve perfect count of boys, girls, males and females according to department

Patient_ref_master

Sr_No   int
Receipt_no  varchar(50)
old_new int
Pat_catagory    int
Fees    decimal(10, 2)
Pat_ID  int
Doc_ID  int
Dept_ID int
Age_Catagory    int
Age decimal(10, 7)
creation_Date   datetime
created_By  int
Qty decimal(5, 2)
Actual_Amnt decimal(7, 4)
Receipt_type    int
Receipt_total_Price decimal(7, 4)
Receipt_suggestion_Type int
Receipt_actual_suggestion   int

Patient master

Pat_Code    int
Pat_FName   varchar(30)
Pat_MName   varchar(30)
Pat_SName   varchar(30)
Pat_Sex varchar(10)
Pat_Addr    varchar(100)
Pat_Mob_No  varchar(13)
age int
creation_Date   datetime
created_By  int

Please describe the Patient_Ref_master and Patient_Master tables. The answer will be a series of CASE statments, but it is going to be hard to write them without knowing the table structures. — Declan_K, Commented Aug 5, 2013 at 16:48
as noted in the comment above, without the table definitions, we can not determine the problem. also, consider including a dump of the table contentx (the 20 rows). Clearly one of your rows falls outside the query selection criteria. — DwB, Commented Aug 5, 2013 at 16:55

i-one · Accepted Answer · 2013-08-05 19:47:11Z

Though I'm not confident without data sample, but give a try to following:

;with cte as (
    SELECT prm.Dept_ID, p.[Group], prm.Sr_No
    FROM Patient_Ref_master prm
        JOIN Patient_Master pm ON pm.Pat_Code = prm.Pat_ID
        CROSS APPLY (
            select
                case
                    when pm.Age between 0 and 16 and pm.Pat_Sex = 1 then 'Boys'
                    when pm.Age between 0 and 16 and pm.Pat_Sex = 2 then 'Girls'
                    when pm.Age > 16 and pm.Pat_Sex = 2 then 'Females'
                    when pm.Age > 16 and pm.Pat_Sex = 1 then 'Males'
                end as [Group]
        ) p
    WHERE prm.creation_Date = '2013/08/02'
)
select Dept_ID, [Boys], [Girls], [Females], [Males]
from cte
    pivot (count(Sr_No) for [Group] in ([Boys],[Girls],[Females],[Males])) p

I removed left join to Gender_master and Dept_Master, since I do not see use of them. Also I changed join from left to inner between Patient_Ref_master and Patient_Master.

Collectives™ on Stack Overflow

Get proper count of the rows from table

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related