How can I get aggregate counts without repeating first column in group by clause?

Question

This query is forcing me to put f.CONCEPT_CD in the group by clause because I'm using it in the case statements in the select clause. How do I keep the counts in the result set, without repeating records with the same f.PATIENT_NUM? Notice how PATIENT_NUM = 5 is repeated in the screen shot below. I only want to show one record if PATIENT_NUM = 5.

SELECT 
    f.PATIENT_NUM,  
    CASE WHEN f.CONCEPT_CD = 'BIO|HGT' THEN COUNT(NVAL_NUM) ELSE 0 END AS 'HEIGHT', 
    CASE WHEN f.CONCEPT_CD = 'BIO|WGT' THEN COUNT(NVAL_NUM) ELSE 0 END AS 'WEIGHT', 
    CASE WHEN f.CONCEPT_CD = 'BIO|BMI' THEN COUNT(NVAL_NUM) ELSE 0 END AS 'BMI', 
    CASE WHEN f.CONCEPT_CD = 'BIO|DIA' THEN COUNT(NVAL_NUM) ELSE 0 END AS 'DIA', 
    CASE WHEN f.CONCEPT_CD = 'BIO|SYS' THEN COUNT(NVAL_NUM) ELSE 0 END AS 'SYS' 
  FROM [HIMC_I2B2_LZ-PROD].[dbo].[I2B2_OBSERVATION_FACT] f
  JOIN [HIMC_I2B2_LZ-PROD].[dbo].[I2B2_CONCEPT_DIMENSION] d ON f.CONCEPT_CD = d.CONCEPT_CD 
  GROUP BY f.PATIENT_NUM, f.CONCEPT_CD

enter image description here

John Woo · Accepted Answer · 2013-04-17T00:38:59.413

SELECT  f.PATIENT_NUM,  
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|HGT' THEN 1 END) AS 'HEIGHT', 
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|WGT' THEN 1 END) AS 'WEIGHT', 
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|BMI' THEN 1 END) AS 'BMI', 
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|DIA' THEN 1 END) AS 'DIA', 
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|SYS' THEN 1 END) AS 'SYS' 
FROM    [HIMC_I2B2_LZ-PROD].[dbo].[I2B2_OBSERVATION_FACT] f
        INNER JOIN [HIMC_I2B2_LZ-PROD].[dbo].[I2B2_CONCEPT_DIMENSION] d 
            ON f.CONCEPT_CD = d.CONCEPT_CD 
GROUP   BY f.PATIENT_NUM

You can also use PIVOT() which is supported in sql server,

SELECT  PATIENT_NUM,
        [BIO|HGT] AS 'HEIGHT',
        [BIO|WGT] AS 'WEIGHT',
        [BIO|BMI] AS 'BMI',
        [BIO|DIA] AS 'DIA',
        [BIO|SYS] AS 'SYS'
FROM
    (
        SELECT  f.PATIENT_NUM, f.CONCEPT_CD
        FROM    [HIMC_I2B2_LZ-PROD].[dbo].[I2B2_OBSERVATION_FACT] f
                INNER JOIN [HIMC_I2B2_LZ-PROD].[dbo].[I2B2_CONCEPT_DIMENSION] d 
                    ON f.CONCEPT_CD = d.CONCEPT_CD 
    ) org
    PIVOT
    (
        COUNT(*)
        FOR PATIENT_NUM IN ([BIO|HGT],[BIO|WGT],[BIO|BMI],
                            [BIO|DIA],[BIO|SYS])
    ) pvt

UPDATE 1

SELECT  f.PATIENT_NUM,  
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|HGT' THEN 1 END) AS 'HEIGHT', 
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|WGT' THEN 1 END) AS 'WEIGHT', 
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|BMI' THEN 1 END) AS 'BMI', 
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|DIA' THEN 1 END) AS 'DIA', 
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|SYS' THEN 1 END) AS 'SYS' 
FROM    [HIMC_I2B2_LZ-PROD].[dbo].[I2B2_OBSERVATION_FACT] f
        INNER JOIN [HIMC_I2B2_LZ-PROD].[dbo].[I2B2_CONCEPT_DIMENSION] d 
            ON f.CONCEPT_CD = d.CONCEPT_CD 
GROUP   BY f.PATIENT_NUM
HAVING  COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|HGT' THEN 1 END) >  0 AND
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|WGT' THEN 1 END) >  0 AND 
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|BMI' THEN 1 END) >  0 AND 
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|DIA' THEN 1 END) >  0 AND 
        COUNT(CASE WHEN f.CONCEPT_CD = 'BIO|SYS' THEN 1 END) >  0

But I don't want the value of NVAL_NUM, I want the count of records. — JustBeingHelpful, Apr 17 '13 at 00:32
Nice! Is there a way to only show the records if the count > 0 for all of the aggregate alias columns? — JustBeingHelpful, Apr 17 '13 at 00:37
add a `HAVING` clause to filter the aggregated result. updated `:D` — John Woo, Apr 17 '13 at 00:41

How can I get aggregate counts without repeating first column in group by clause?

1 Answers1